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

August 2017




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


Published by aakai1056. Latest update on November 3, 2009 at 06:23 AM by aakai1056.
This document, titled "How to Create Msg/Popup to notifications in Excel VBA?," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).