I need a Excel VB script to notify/popup a message when the number of the letter U on a calendar exceeds 6, 8, 12 etc. It will have to check the range of the calendar searching for the letter U and adding them and then providing an automatic popup when the numbers of U's are greater than 6 and etc
You stated you wanted the msgbox to pop up automatically, so the code will have to go into the worksheet change event and not just a regular macro or command button. Here is the code but I also have a link for you to check the file out. I made a February 2009 calendar and just added random words with the letter "U" in them. The total is at six words. Type in a word or words that you would actually use, and see if that is what you need. Hope this helps.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Variant
letter = LCase("u")
letter2 = UCase("U")
Dim count As Integer
Dim FindU As Range
Set FindU = Range("A4:G12")
For Each i In FindU
If InStr(i, letter) > 0 Or InStr(i, letter2) > 0 Then
count = count + 1
temp = count
Select Case temp
Case Is > 12
MsgBox "The number of U's have exceeded 12." & " The total is " & temp
Case Is > 8
MsgBox "The number of U's have exceeded 8." & " The total is " & temp
Case Is > 6
MsgBox "The number of U's have exceeded 6." & " The total is " & temp
Thanks to Helper
for this tip on the forum.
Published by aakai1056
Latest update on November 3, 2009 at 06:23 AM by aakai1056.