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

December 2016




Issue



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.

Solution


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.

[URL=http://www.4shared.com/file/87077596/56259f70/Calendar.html]Calendar.xls/URL


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
b

Note


Thanks to Helper for this tip on the forum.

Related :

This document entitled « How to Create Msg/Popup to notifications in Excel VBA? » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.