CODEWITHSUNDEEP
excelvba
Our Man in Bananas
Our Man in Bananas

Reputation: 5981

using worksheet events in ThisWorkbook class module

I have a workbook with a lot of worksheets, and want the below code to run when the event is fired by the worksheet...except I don't want to copy the code to all the worksheets

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$55" Then
         Target.Worksheet.Range(Range("A" & Left(Target.Text, 2)), Range("AE" & Left(Target.Text, 2))).Select
    End If
End Sub

so I tried the below in the ThisWorkbook class module, but I must have got it wrong, as it's not firing the event

Dim WithEvents xSheet As Worksheet

Private Sub xSheet_Change(ByVal Target As Range)
    If Target.Address = "$B$55" Then
         Target.Worksheet.Range(Range("A" & Left(Target.Text, 2)), Range("AE" & Left(Target.Text, 2))).Select
    End If
End Sub

What's the correct way to do this?

Upvotes: 0

Views: 421

Answers (1)

SteveES
SteveES

Reputation: 554

Assuming you want the above to run for all worksheets rather than specific worksheets, you could try using the Workbook_SheetChange event in ThisWorkbook. E.g.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address = "$B$55" Then
         Sh.Range(Range("A" & Left(Target.Text, 2)), Range("AE" & Left(Target.Text, 2))).Select
    End If
End Sub

Upvotes: 1

Related Questions