Run macro when user selects a different sheet

[CraigKer]

I need to run a macro when a user selects a different sheet within my
workbook. I tried using the following code as a test in module 1 with some
other code i wrote:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.Name = "Info" Then
MsgBox "made it to info"
Else
MsgBox "not info"
End If
End Sub

However, this does not work. When I change the active sheet in the workbook
nothing happens. Do I need to put this code somewhere else?

Thanks in advance...

 

[Mike H]

Hi,

Your code is fine so I guess you have it in the wrong place. It's 'Workbook'
event code so double click 'This Workbook' and paste the code in on the right.

Mike

 

[Dave Peterson]

On top of Mike's response, I'd use:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If sh.Name = "Info" Then
MsgBox "made it to info"
Else
MsgBox "not info"
End If
End Sub

Actually, I'd use:
If lcase(sh.Name) = lcase("Info") Then
just to avoid a difference in upper/lower case.

 

[CraigKer]

Thanks for the reply.
In my Excel workbook when the VB editor is open it says (Generl) and
Workbook_SheetActive at the top and this is for Module1 (Code). I guess I
don't understand what you mean when you say "double click 'This Workbook' and
paste the code in on the right"?

When I change the active sheet in the workbook by clicking on antoher sheet
nothing happens. Unless the msg box is popping up and closing itself before
I actually see it?