Hello Masters,

Is there a way to put sheet activate and deactivate events (for 40
sheets) in a common module, ie. inside a worksheet code module?

Currently I have the codes in the respective sheets. I need all these
codes to be in one sheet.

Is this possible?

Thanks!

-DC

Maybe use the Workbook_SheetActivate event ?
It's in the ThisWorkbook module.

NickHK

"Dileep Chandran" wrote in message
ups.com...
Hello Masters,

Is there a way to put sheet activate and deactivate events (for 40
sheets) in a common module, ie. inside a worksheet code module?

Currently I have the codes in the respective sheets. I need all these
codes to be in one sheet.

Is this possible?

Thanks!

-DC

Hi Nick,

Thanks for your time. But it doesnt seems to work. Here is the code I
put in ThisWorkbook module:


Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If Sh.CodeName = "Sheet1" Then
MsgBox("Test1")

Else

If Sh.CodeName = "Sheet2" Then
MsgBox("Test2")

End if
End if

End Sub

Any idea what went wrong?

-DC

Your workbook event code works fine for me, try this too

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim sCodeName As String

sCodeName = Sh.CodeName

Select Case sCodeName
Case "Sheet1"
MsgBox "Sheet1", , "Case Sheet1"

Case Else
MsgBox sCodeName, , "Case Else"
End Select

End Sub

Bear in mind you might not be able to return codenames of newly insterted
sheets until the wb has been saved or without further intervention.

Regards,
Peter T


"Dileep Chandran" wrote in message
oups.com...
Hi Nick,

Thanks for your time. But it doesnt seems to work. Here is the code I
put in ThisWorkbook module:


Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If Sh.CodeName = "Sheet1" Then
MsgBox("Test1")

Else

If Sh.CodeName = "Sheet2" Then
MsgBox("Test2")

End if
End if

End Sub

Any idea what went wrong?

-DC

Thanks Nick & Peter!

I have one more question. Is it possible to put this code in any
worksheet code module other than the ThisWorkbook module?

Thanks for your continued support.

-DC

I thought you wanted some centralised way to handle the Activate event ?
This provides it, as it fires for all worksheets.

If you put similar code in a worksheet module, it will only fire for that
specific worksheet, which is what you want to avoid (No ?).

NickHK

"Dileep Chandran" wrote in message
oups.com...
Thanks Nick & Peter!

I have one more question. Is it possible to put this code in any
worksheet code module other than the ThisWorkbook module?

Thanks for your continued support.

-DC