Create Msg/Popup to notify in Excel VBA
Solved/Closed
Related:
- Create Msg/Popup to notify in Excel VBA
- Number to words in excel without vba - Guide
- Create skype account with gmail - Guide
- Send popup message to another computer on network windows 10 - Guide
- Enable vba in excel - Guide
- Vba case like - Guide
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.
[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 Sub
[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 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..
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.
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.
