Create Msg/Popup to notify in Excel VBA

Siebel SME - Feb 10, 2009 at 10:17 AM
 Siebel SME - Feb 11, 2009 at 09:48 PM

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 number of U's are greater than 6 and etc. Thanks..

2 responses

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
Dim letter
letter = LCase("u")
letter2 = UCase("U")
Dim count As Integer
Dim FindU As Range
Set FindU = Range("A4:G12")
Dim temp

For Each I In FindU

If InStr(i, letter) > 0 Or InStr(i, letter2) > 0 Then

count = count + 1

temp = count

End If

Next i

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

End Select

End Sub
2 Questions:

When I try to run the Macro/VB Script it does nothing but come up with the Macros prompt to create a macro name. What do I do?

Is it different if I want to find the word "Unscheduled" in the calendar?

Thanks so much for your help..
1) Did you copy and paste the code in you own worksheet? Where did you put the code?

2) It will not matter if the word is uppercase or lower case. This part is very important. The IntStr() is used so the way it is coded it will find the first occurance of "U" and count the position in the word and stop. So, the word "Unscheduled" only counts one "U" because it stops the first time it sees the character. Do you need the total to be one or two for that word? If you need it to count two, then that will be trickier to code.
Siebel SME > Helper
Feb 11, 2009 at 09:48 PM
I figured it out Helper.. thank you so much.. never mind. I got what you meant. MY new greater question is here: 67595 excel vba for multiple worksheets