Run macro when Sheets are active

  • Thread starter Thread starter Ed Davis
  • Start date Start date
E

Ed Davis

I would like to run a macro on about 12 of 31 sheets when they are
activated.
I was hoping I could do this without putting the macro in to each worksheet
code. Is it possible to do it.
 
In ThisWorkbook use the sheet activate event
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'code here
End Sub

you will just have to exclude sheets you dont want to use, something like
If Sh.Name <> Sheet2 then

End If

Sh is the sheet that is firing the activation.
 
Hi Ed

You can have the macro in the code sheet for ThisWorkbook. This macro will
run 'Your code' when Sheet1, Sheet2 and sheet4 is activated, but not if
Sheet3 is activated.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ShArr As Variant
Dim RunMacro As Boolean
ShArr = Split("Sheet1,Sheet2,Sheet4", ",") 'All sheets where macro shall run
For sCounter = 0 To UBound(ShArr)
If Sh.Name = ShArr(sCounter) Then
RunMacro = True
Exit For
End If
Next
If RunMacro = True Then
'Here goes your code
End If
End Sub

Regards,
Per
 
Thank you very much.
That did the trick.
Greatly appreciated


--
Thank You in Advance
Ed Davis
Per Jessen said:
Hi Ed

You can have the macro in the code sheet for ThisWorkbook. This macro will
run 'Your code' when Sheet1, Sheet2 and sheet4 is activated, but not if
Sheet3 is activated.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ShArr As Variant
Dim RunMacro As Boolean
ShArr = Split("Sheet1,Sheet2,Sheet4", ",") 'All sheets where macro shall
run
For sCounter = 0 To UBound(ShArr)
If Sh.Name = ShArr(sCounter) Then
RunMacro = True
Exit For
End If
Next
If RunMacro = True Then
'Here goes your code
End If
End Sub

Regards,
Per
Click to expand...
 
Back
Top