Public Worksheet Events

[tiredofit]

Is there any reason why worksheet events, such as:

Private Sub Worksheet_Activate()

    Msgbox "Hi"

End Sub

are private?

I am thinking of changing it to Public so it can be called elsewhere. Is that a bad idea and might it cause problems?

Alternatively, I can leave it as Private but put the code in a separate module, like this:

Private Sub Worksheet_Activate()

    Call ModHi.Hi

End Sub

and have a module called ModHi:

Public Sub Hi()

    Msgbox "Hi"

End Sub

Thanks

 

[Joe4]

I am thinking of changing it to Public so it can be called elsewhere. Is that a bad idea and might it cause problems?

Yes, event procedure code runs automatically by certain triggered action, and are not meant to be called.

If you want to call it, put the code in another separate public procedure, and you can call that code within your Worksheet event code, or from wherever else you want.
That way you only have to write the code once, but can call it from multiple places.

 

[tiredofit]

Yes, event procedure code runs automatically by certain triggered action, and are not meant to be called.

If you want to call it, put the code in another separate public procedure, and you can call that code within your Worksheet event code, or from wherever else you want.
That way you only have to write the code once, but can call it from multiple places.

Thanks, so as per my alternative solution?

 

[Joe4]

Thanks, so as per my alternative solution?

Yep. That is exactly the way you want to do it.
Don't try messing with the Event Procedures.

 

[tiredofit]

Yep. That is exactly the way you want to do it.
Don't try messing with the Event Procedures.

Thanks for the help. I'll take that approach.

I've been thinking about this for some time but was afraid someone else might ask why is (in my example) the code Sub Hi NOT written in the Sheet's area.

It contradicts the principle "code belonging to a sheet should reside there".

 

[Joe4]

It contradicts the principle "code belonging to a sheet should reside there".

Actually, I wouldn't say that is entirely true (especially when you may have code that interacts between multiple sheets).
The principle that I usually follow is that I typically only put Event Procedure code in the Sheet modules, and put all other code in General Modules (where it is easily accessible from any sheet).

 

[tiredofit]

Actually, I wouldn't say that is entirely true (especially when you may have code that interacts between multiple sheets).
The principle that I usually follow is that I typically only put Event Procedure code in the Sheet modules, and put all other code in General Modules (where it is easily accessible from any sheet).

Noted and thanks again.

 

[Joe4]

You are welcome.