How to Create Msg/Popup to notifications in Excel VBA?

Ask a question


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 
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 Su


Thanks to Helper for this tip on the forum.

Excel - Send value of cell to target
Repainting a cell using excel VBA