Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to find cell content in sheets and make sheet active | Excel Discussion (Misc queries) | |||
macro active sheet | Excel Discussion (Misc queries) | |||
Selecting Embedded charts in Active Sheets programmatically | Charts and Charting in Excel | |||
MACRO with Active X | Excel Discussion (Misc queries) | |||
In 3 active sheets in wkbk, determine& display the # of sheets that have data | Excel Discussion (Misc queries) |