Run macro when Sheets are active
 

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.

--
Thank You in Advance
Ed Davis

Run macro when Sheets are active
 

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

"Ed Davis" skrev i meddelelsen
...
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.

--
Thank You in Advance
Ed Davis

Run macro when Sheets are active
 

Thank you very much.
That did the trick.
Greatly appreciated


--
Thank You in Advance
Ed Davis
"Per Jessen" wrote in message
...
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

"Ed Davis" skrev i meddelelsen
...
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.

--
Thank You in Advance
Ed Davis