• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pop Up message on multiple worksheets.

Frncis

Frncis

Member
I know that if you want a message to pop up on a specific sheet, you click on the worksheet tab, view code, & add the code. However I am trying to add the code to thisworkbook, so the code does not have be on each sheet. I know that the code below is missing something, but I can't figure out what.
Code: 
Private Sub Worksheet_Activate()
Select Case Sh.Name
'These are the worksheets here that are not to be called with activation.
        Case "Sheet1", "Sheet11", "Sheet21", "Sheet31", "Sheet41", "Sheet42", "Sheet43", "Sheet44", "Sheet 45", "Sheet46", "Sheet47", "Sheet48", "Sheet49", "Sheet50", "Sheet51", "Sheet52", "Sheet53", "Sheet54", "Sheet55", "Sheet56", "Sheet57", "Sheet58"
        MsgBox "Please check the year of the next meeting date, & correct as necessary!", vbInformation, "Kutools for Excel"
    End Select
End Sub
I forgot to mention that when I tested the code nothing happened.
 
Last edited:
I only checked this against Sheet 1. Trust it works for all the sheets ...


Code: 
Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
'These are the worksheets here that are not to be called with activation.
      Case "Sheet1", "Sheet11", "Sheet21", "Sheet31", "Sheet41", "Sheet42", "Sheet43", "Sheet44", "Sheet 45", "Sheet46", "Sheet47", "Sheet48", "Sheet49", "Sheet50", "Sheet51", "Sheet52", "Sheet53", "Sheet54", "Sheet55", "Sheet56", "Sheet57", "Sheet58"
        MsgBox "Please check the year of the next meeting date & correct as necessary!", vbInformation, "Review Meeting Date"
    End Select
End Sub
 
Logit said:
I only checked this against Sheet 1. Trust it works for all the sheets ...


Code: 
Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
'These are the worksheets here that are not to be called with activation.
      Case "Sheet1", "Sheet11", "Sheet21", "Sheet31", "Sheet41", "Sheet42", "Sheet43", "Sheet44", "Sheet 45", "Sheet46", "Sheet47", "Sheet48", "Sheet49", "Sheet50", "Sheet51", "Sheet52", "Sheet53", "Sheet54", "Sheet55", "Sheet56", "Sheet57", "Sheet58"
        MsgBox "Please check the year of the next meeting date & correct as necessary!", vbInformation, "Review Meeting Date"
    End Select
End Sub
Click to expand...
I forgot to mention that when I tested the code nothing happened.
 
Just tested here with Sheet 1 and a Sheet 11. It works here. ???

Did you paste the macro into the ThisWorkbook module ?
 
Logit said:
Just tested here with Sheet 1 and a Sheet 11. It works here. ???

Did you paste the macro into the ThisWorkbook module ?
Click to expand...
Yes. The pages listed are the ones that I don't want the message. However I do want the message on pages 2-10, 12 - 20, etc.
 
Frncis
Your code works opposite!

Code: 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Select Case Sh.Name
' name here those which NO NEED msgbox '' I left only one here!
        Case "Sheet1"
            x=x
        Case else
            MsgBox "Please ...", vbInformation, "Review Meeting Date"
    End Select
End Sub
 
Last edited:
Logit said:
Change the sheet names in the CASE statement.
Click to expand...
I get a syntax error on your first line PrivateSub Workbook_SheetActivate(ByVal Sh AsObject)

Not quite sure what you mean by change sheet names. Sorry that I am so thick.
 
vletm said:
Frncis
Did You skip my version?
for Your case 'sheets'
write eg x=x ' nothing!
'drop' Your Msgbox... under case else
Click to expand...
I am still getting a syntax error on your first line. I also hade the message under case else.
I put code on the appropriate sheets until I can get the code on this workbook.
 
Logit thank you for point out my error. You re correct. I needed to look at the solution in the morning.

Veltm Thank you for the solution. I am a big fan of simple code, & code that can be used several time, but written once.
 
You must log in or register to reply here.
Back
Top