Worksheet change event

[Mr930]

I had some VBA code in a sub that triggered on a worksheet change event. The range I was checking was on one tab of the spreadsheet. I now want to check ranges on 8 different tabs. When I set this up, the worksheet_change() function is not called unless I change something on the original tab where it was set up. I thought the change would be caught no matter where it occurred. Is it tab specific?

thanks
Fred Emmerich

 

[GTO]

Hi Fred,

Instead of using the partiticular sheet's event procedure, go to the ThisWorkbook module and try Workbook_SheetChange.

Hope that helps,

Mark

 

[Mr930]

Never heard of that one! thanks!

 

[CPerdue]

'Fraid so. Each tab is a sheet.

You are ahead of me, I spent half a day figuring out that the macro had to be on a specific sheet instead of in a public module.

I have managed to make an event on one sheet call a public macro...perhaps you could have a 'detector' event in each sheet then one public function to do the work? Might have to pass in data.

Please let me know if you find any other useful events.

Thanks,

C.

Edit: Thanks GTO, me neither.

 

[GTO]

Happy to help

You can use the events related to worksheets under ThisWorkbook with the included arguments to have different things happen depending upon what sheet is firing the event. Just by example:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    Select Case Sh.CodeName
    Case "Sheet1", "Sheet2"
        MsgBox "Do something"
    Case "Sheet3"
        MsgBox "or something else"
    Case Else
        MsgBox "or some generic thing you want for any other sheet"
    End Select
End Sub