Macro for Message Pop-up

[rhmkrmi]

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.

 

[Rick Rothstein]

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...

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...

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.

 

[rhmkrmi]

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.

 

[Rick Rothstein]

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.

 

[rhmkrmi]

Excellent.

Thank you so much for your help, I do appreciate it.

 

[rhmkrmi]

Can I also ask if we can make some words bold or change their color in the message box?

Thank you.

 

[rhmkrmi]

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.

 

[Rick Rothstein]

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)?

 

[rhmkrmi]

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.

 

[Rick Rothstein]

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?