Macro for Message Pop-up

rhmkrmi

Active Member
Joined
Aug 17, 2012
Messages
336
Office Version
  1. 365
Platform
  1. Windows
Hi, Can you please help me with a macro that pops up one message when we go into a sheet and another when we move out of that sheet or close the workbook?

In case this is too hard, I can use just the one message.

I also need help adding this macro to some more that already exist in the sheets.

Thank you.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You would not do what you asked with macros, rather, you would do it with event code.

For the MessageBox when going into or leaving a worksheet, right click that worksheet's tab and select "View Code" from the popup menu that appears, then copy/paste the following code into the code window that opened up...
Code:
Private Sub Worksheet_Activate()
  MsgBox "You just entered this sheet"
End Sub

Private Sub Worksheet_Deactivate()
  MsgBox "You just left the sheet"
End Sub
Next, look over to the left of the VBA editor for a window labeled "Project-VB Project" and locate the item in that window named ThisWorkbook and double-click it, then copy/paste the following code into the code window that opens up...
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
  MsgBox "You have just closed this workbook"
End Sub
That's it... go back to the worksheet and leave the worksheet, then enter it again. Finally, close the workbook. Of course, you can change the messages that the MessageBox displays.
 
Upvote 0
Thanks Rick, this is perfect.

I currently have some macros in the same sheet and need help adding your first event code to them.

This is what exists in the sheet when I click on "View Code":

Private Sub Worksheet_Change(ByVal Target As Range)


' ***BLOCK1***
Dim sUndoList As String


On Error Resume Next


If Not Intersect(Target, Range("A1:ZZ1000")) Is Nothing Then
sUndoList = CommandBars.FindControl(ID:=128).List(1)
If Left(sUndoList, 5) = "Paste" Or sUndoList = "Auto Fill" Or sUndoList = "Drag and Drop" Then
Application.EnableEvents = False
Application.Undo
Application.OnUndo "", ""
Application.EnableEvents = True
End If
End If



' ***BLOCK2***
Dim rng As Range
Dim cell As Range
Dim rw As Long


' See if any cells updated in column A
Set rng = Intersect(Target, Range("A:A"))
If rng Is Nothing Then Exit Sub


Application.EnableEvents = False


' Loop through updated cells in column A
For Each cell In rng
rw = cell.Row
Select Case cell.Value
Case "School"
Range(Cells(rw, "H"), Cells(rw, "S")) = "N/A"
Range(Cells(rw, "H"), Cells(rw, "S")).Interior.Color = 15132390
Case Else
Range(Cells(rw, "H"), Cells(rw, "S")) = ""
Range(Cells(rw, "H"), Cells(rw, "S")).Interior.Pattern = xlNone
End Select
Next cell

Application.EnableEvents = True


End Sub


How can I add this event code to the above:

Private Sub Worksheet_Activate()
MsgBox "You just entered this sheet"
End Sub


Private Sub Worksheet_Deactivate()
MsgBox "You just left the sheet"
End Sub

Thank you very much.
 
Upvote 0
How can I add this event code to the above:
The two procedures I posted are independent of the Change event that you currently have, so nothing special has to be done... just place them at the bottom of the code window underneath the last "End Sub" statement.
 
Upvote 0
Excellent.

Thank you so much for your help, I do appreciate it.
 
Upvote 0
Can I also ask if we can make some words bold or change their color in the message box?

Thank you.
 
Upvote 0
I also need another pop-up if in range A1:A20 it finds "Activate" and in range B1:B20 it finds "Home" words.
"Activate" and "Home" are invalid combinations in those ranges.

Can this one also be placed at the bottom of the code window underneath the last "End Sub" statement?

Thank you.
 
Upvote 0
Can I also ask if we can make some words bold or change their color in the message box?
This cannot be done... all the text properties in a MessageBox have to be the same.



I also need another pop-up if in range A1:A20 it finds "Activate" and in range B1:B20 it finds "Home" words.
When should this occur... when the user types into one of the cells in the range A1:A20 or at some other time (if so, when)?
 
Last edited:
Upvote 0
Thanks Rick.

This should happen when, in the same row, we have "Activate" in range A1:A20 and "Home" in range B1:B20.

Thanks again.
 
Upvote 0
This should happen when, in the same row, we have "Activate" in range A1:A20 and "Home" in range B1:B20.

That does not tell me what I need to know. I am trying to find out in what event the code should go in. What will be occurring when you want this MessageBox to popup... will the user be typing values into one of the cells in A1:A20? do you want it check when the worksheet activates and/or deactivates? when a certain cell is selected? some other time?
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top