use/ reference VBA Worksheet event in module for use in all workbooks

[stevewingjr]

Hi there, I'm very new to VBA, first of all, so thank in advance for patience.

I have this code saved to "ThisWorkbook" on my PERSONAL workbook:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
MsgBox "Name of Sheet : " & Sh.Name
End Sub

it works fine when I'm within the PERSONAL workbook itself, but doesn't seem to do anything when I change sheets in a different workbook (since it's a workbook-specific event).

Is there any way to change this to an application event to run whenever I activate (more specifically, switch between) any worksheet in any open workbook in excel? or call/run this event in a module (this doesn't necesarily have to launch when excel opens, I'm fine with launching a macro to start looking for this event)?

Just changing "Private Sub" to "Public Sub" doesn't really seem to do anything

MsgBox "Name of Sheet :"& Sh.Name
is just a space holder, ultimately the macro will check if the active sheet is connected to a database and return what certain database preferences have been set to (unique to that worksheet's connection). As I can have multiple worksheets connected to multiple instances of databases, I plan on using this as a reminder of what the preferences of each connection are when I open them.

I've thought about using application.ontime, but I don't need it to run THAT regularly, and the information is really database worksheet specific.

Maybe I'm not thinking about this correctly, so maybe there's a better way to do this. Any help would be appreciated. Thanks!

 

[pgc01]

Hi Steve
Welcome to the board

You need to use an Application event, in this case the Application.SheetActivate Event.

If you are not familiar with Application Events, check Chip Pearson's page:

Excel Application Events And VBA

 

[stevewingjr]

Very helpful! Thanks for the link to Chip Pearson's page too. That was absolutely the missing link.

Much appreciated.

 

[pgc01]

You're welcome. Thanks for the feedback.