Creating Message Boxes

[davidzan]

I have several sheets within a workbook. How can I create a different "message box" on more than one sheet within my workbook. Below is the message box I created on the initial Sheet:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

On Error Resume Next

If Sh.Name = "Sheet1" Then

MsgBox "line1" & vbCrLf & "line2" & vbCrLf & "line3" & vbCrLf & "line4",vbinformation, "Created 02/09/2024

End If

End Sub

 

[Joe4]

Welcome to the Board!

Probably best to use a Case statement, structured something like this here, if you want a different message for each sheet:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next

Select Case Sh.Name
    Case "Sheet1"
        MsgBox "line1" & vbCrLf & "line2" & vbCrLf & "line3" & vbCrLf & "line4", vbInformation, "Created 02/09/2024"
    Case "Sheet2"
        MsgBox ...
    Case "Sheet3"
        MsgBox ...
    ...
End Select
       
End Sub

If the messages are similar and can be programmed, we may be able to simplify it a bit. We would just need to know more details about it.

 

[davidzan]

Welcome to the Board!

Probably best to use a Case statement, structured something like this here, if you want a different message for each sheet:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next

Select Case Sh.Name
    Case "Sheet1"
        MsgBox "line1" & vbCrLf & "line2" & vbCrLf & "line3" & vbCrLf & "line4", vbInformation, "Created 02/09/2024"
    Case "Sheet2"
        MsgBox ...
    Case "Sheet3"
        MsgBox ...
    ...
End Select
      
End Sub

If the messages are similar and can be programmed, we may be able to simplify it a bit. We would just need to know more details about it.

Thanks so much. I tried for a long time to accomplish this task. This worked great.

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[davidzan]

You are welcome.
Glad I was able to help!

Yes you did. I researched for hours on the web for this solution. Hope others will benefit from this post.