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

Last update on November 3, 2009 10:23 AM by aakai1056
Published by aakai1056

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




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.
Best answers for « How to Create Msg/Popup to notifications in Excel VBA? » in :
[VBA: VB6] Using excel from another application Show[VBA: VB6] Using excel from another application Here is a little routine to call Excel from VB6 or another Office application. Paste in a general module (eg Module1) In VBA>> Insert>> Module and paste in the window ... In VB6>>...
VBA A simple second Timer ShowVBA A simple second Timer In VBA, there is Timer feature available,but you can create one very easily. In a module sheet: Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'For example: Start / Stop the timer every...
Inserting an Hyperlink in an Excel Worksheet ShowCreating an Hyperlink in an Excel Worksheet Introduction Implementation Linking to a Website Linking to a document Linking to another cell in the spreadsheet Notes Introduction Excel supports the use of hyperlinks and it is a very...
Download XLS (Excel) to DBF Converter ShowDescription The application is designed by WhiteTown Software. XLS (Excel) to DBF Converter is a tool that enables you to convert your XLS files to DBF format quickly and easily. Simple and easy to use, the application has been awarded from various...
Hyperlinks ShowIntroduction to anchors Hypertext links or hyperlinks (anchors) are HTML elements that, when clicked on, enable readers to visit a new address. Hyperlinked text is underlined by default. Hyperlinks are what connect web pages to one another. They...
They need your help