trigger an action when new sheet is selected

[Nick Vittum]

I'm trying to make a an action occur whenever I shift from one worksheet to another. Would I have to write a macro and then call that macro from every worksheet's activate event? Or is there a way to write it into a Workbook_change event? Or some other way entirely?

In my case, it's a simple bit of code, and only 12 worksheets are affected, so it's not a big deal. But I'm just wondering if my approach is the usual one, or if there's something simpler.

[HansV]

The Workbook object has a Workbook_SheetActivate event. The code for this event goes into the ThisWorkbook module.
The event procedure has an argument Sh that represents the sheet being activated.
Example:

Code: Select all

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    MsgBox "You activated " & Sh.Name
End Sub

S3137.png

[Nick Vittum]

Okay, that's exactly what I was looking and hoping for. Perfect. Thanks once again.

[Nick Vittum]

Based on what you told me, I wrote

Code: Select all

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    UF_Categories.Combo_sheets.Value = Sh.Name
End Sub

. It works perfectly. Whenever I change sheets manually, Combo_Sheets uptdate automatically, and the form data with it. But when I enter a similar line here—

Code: Select all

Private Sub UserForm_Initialize()
    Me.Combo_sheets.Value = Sh.Name
    (etc)
End sub

—trying to have the form automatically open to the sheet that's open, it kicks up a fuss. I tried "ActiveSheet.Sh.Name" and "ActiveSheet(Sh.Name)" as well. Nothing I could think of was accepted.

What am I doing wrong?

[HansV]

I will get back to you later.

[Nick Vittum]

I will get back to you later.

Thanks. I found it. The proper syntax, apparently, is

Code: Select all

    Me.Combo_sheets.Value = ActiveSheet.Name

Let me know if that's wrong, or if there's a better way. So far, it seems to work.