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

You are right, Nick. But I have to move this codes along when we copy
a particular sheet (A sheet common to all workbooks, Workbook code
name is Testing) to another workbook.

Can it be done?

-DC

Put similar code in both your ThisWorkbook module and in the 'common' sheet
module that might get copied, obviously the event procedure names will be
slightly different.

In the ThisWorkbook module early abort if the sheet is the 'common' sheet as
similar code will already have been processed in the sheet event code. Eg -

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

sName = UCase(Sh.Name)

Select Case Sh.Name
Case "COMMON"
'do nothing, already handled in that sheet's event code
Case "SHEET2"
' Sheet2 code
Case Else
' code for other sheets
End Select

End Sub

Regards,
Peter T


"Dileep Chandran" wrote in message
oups.com...


You are right, Nick. But I have to move this codes along when we copy
a particular sheet (A sheet common to all workbooks, Workbook code
name is Testing) to another workbook.

Can it be done?

-DC

Peter,

What I excatly need is to put the entire code (for all the 40 sheets)
in one workbook module other than ThisWorkbook module.

Did I made my requirement clear?

-DC

Did I made my requirement clear?

Indeed you did say you only want code in one module, but you also said you
want to allow for the 'Common' sheet to be copied to other wb's and its
event code to be copied too. On the basis of the limited information you
provided, at times contradictory, I would have thought my suggestion was a
workable compromise.

[all code..] in one workbook module other than ThisWorkbook module.

The only way to do that, trap sheet events in a single module but not
ThisWorkbook module, would be in a 'WithEvents' class module. This could be
set up to trap workbook events (incl sheet activate etc), Worksheet events,
or application events.

Normally the only reason to make your own 'WithEvents' class module for
sheet or wb events would be to trap events in (an)other workbook(s).

It's difficult to know what you want to do within the constraints of VBA,
would one module to trap sheet events in ALL open workbooks suffice, eg

' in a class module named clsAppEvents
Public WithEvents xl As Excel.Application

Private Sub xl_SheetActivate(ByVal Sh As Object)
MsgBox Sh.Name, , Sh.Parent.Name
End Sub

' in normal module

Dim mcApp As clsAppEvents

Sub SetAppEvents()
Set mcApp = New clsAppEvents
Set mcApp.xl = Application
End Sub

Run SetAppEvents then activate sheets in all open workbooks.

Regards,
Peter T


"Dileep Chandran" wrote in message
oups.com...
Peter,

What I excatly need is to put the entire code (for all the 40 sheets)
in one workbook module other than ThisWorkbook module.

Did I made my requirement clear?

-DC

Peter,

I apologize for the confusion created. I have a time tracker. It is a
workbook with only one sheet, ie TimeTracker.

I need to place some codes in the TimeTracker sheet so that when we
move this sheet to Workbook1 for capturing Workbook1 time the codes
should also move along with the sheet. So that, I can refrain from
importing a module or class module to the Workbook1.

Thanks for your continued support.

-DC

Now I'm even more confused! The TimeTracker wb has only one sheet, what
happened to the need to trap events in 40 sheets.

Regards,
Peter T

"Dileep Chandran" wrote in message
oups.com...
Peter,

I apologize for the confusion created. I have a time tracker. It is a
workbook with only one sheet, ie TimeTracker.

I need to place some codes in the TimeTracker sheet so that when we
move this sheet to Workbook1 for capturing Workbook1 time the codes
should also move along with the sheet. So that, I can refrain from
importing a module or class module to the Workbook1.

Thanks for your continued support.

-DC

I am going to move this sheet to another workbook(s) having aroung 30
- 40 sheets.

Make sense now??

Thanks

-DC

Are you are thinking to copy the sheet and its module code to the wb with
30+ sheets, then expect the copied sheet module to trap workbook level or
sheet level events of the 30+ sheets. That will not work.

Why not install the event code directly into Thisworkbook.module of the 30+
sheet wb, in a template perhaps.

Regards,
Peter T

"Dileep Chandran" wrote in message
oups.com...
I am going to move this sheet to another workbook(s) having aroung 30
- 40 sheets.

Make sense now??

Thanks

-DC

Hi Peter,

I am looking for an alternative for this:

Why not install the event code directly into Thisworkbook.module of
the 30+
sheet wb, in a template perhaps.

Because our templates are dynamic.

-DC

I've not heard the term 'dynamic template' before and not sure what that
implies in your context. However, as I've tried to explain, when you copy a
sheet into another wb only the sheet and its sheet module code will be
copied.

To be clear - no other module will be copied with the sheet, the events in
the copied sheet module can only trap events in the copied sheet.

In theory, the activate event in the copied sheet could check if it it's not
the only sheet in the wb, if so check a 'flag' in some cell, if that doesn't
exist it must be a recently copied sheet. Then go on to write event code in
the ThisWorkbook module as described before, and set a flag to avoid doing
same again. But don't go there!

Regards,
Peter T


"Dileep Chandran" wrote in message
ups.com...
Hi Peter,

I am looking for an alternative for this:

Why not install the event code directly into Thisworkbook.module of
the 30+
sheet wb, in a template perhaps.

Because our templates are dynamic.

-DC

Thanks Peter. Got the answer. By dynamic i meant the template changes
every day.

-DC