Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Macro on All Sheets Q
I have a macro that runs a routine on the active sheet, but how would I create a macro that will run this macro on 'all' sheets expect sheet1 & sheet4?
I would prefer to create a new macro and call the original macro from within, so that I have the option to run individually |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Macro on All Sheets Q
wrote:
I have a macro that runs a routine on the active sheet, but how would I create a macro that will run this macro on 'all' sheets expect sheet1 & sheet4? I would prefer to create a new macro and call the original macro from within, so that I have the option to run individually Sub looper Dim ws As Worksheet For Each ws in Sheets ws.Activate call originalMacro Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Macro on All Sheets Q
Here's one example (using SheetProtection) of how I normally handle
routines that I want this kind of flexibility with, that you may get some ideas from for how to structure your project... Sub ResetProtection(Optional Wks As Worksheet) If Wks Is Nothing Then Set Wks = ActiveSheet Wks.Unprotect PWRD: wksProtect Wks End Sub Sub Protect_AllSheets(Optional Wkb As Workbook) Dim Wks As Worksheet If Wkb Is Nothing Then Set Wkb = ActiveWorkbook Application.ScreenUpdating = False For Each Wks In Wkb.Worksheets: ResetProtection Wks: Next Application.ScreenUpdating = True End Sub Sub Unprotect_AllSheets(Optional Wkb As Workbook) Dim Wks As Worksheet If Wkb Is Nothing Then Set Wkb = ActiveWorkbook Application.ScreenUpdating = False For Each Wks In Wkb.Worksheets: wksUnprotect Wks: Next Application.ScreenUpdating = True End Sub ...and the main process routine being called by the above... Sub wksProtect(Optional Wks As Worksheet) ' Protects specified sheets according to Excel version. ' Assumes Public Const PWRD as String contains the password, even if there isn't one. ' ' Arguments: WksName [In] Optional. The name of the sheet to be protected. ' Defaults to ActiveSheet.Name if missing. If Wks Is Nothing Then Set Wks = ActiveSheet On Error Resume Next With Wks If Val(Application.VERSION) = 10 Then .Protect Password:=PWRD, _ DrawingObjects:=CBool(gvWksProtection(WksProtectio n.wpDrawingObjects)), _ Contents:=CBool(gvWksProtection(WksProtection.wpCo ntents)), _ Scenarios:=CBool(gvWksProtection(WksProtection.wpS cenarios)), _ UserInterfaceOnly:=CBool(gvWksProtection(WksProtec tion.wpUserInterfaceOnly)), _ AllowFiltering:=CBool(gvWksProtection(WksProtectio n.wpAllowFiltering)), _ AllowFormattingColumns:=CBool(gvWksProtection(WksP rotection.wpAllowFormatCols)), _ AllowFormattingRows:=CBool(gvWksProtection(WksProt ection.wpAllowFormatRows)), _ AllowFormattingCells:=CBool(gvWksProtection(WksPro tection.wpAllowFormatCells)), _ AllowDeletingColumns:=CBool(gvWksProtection(WksPro tection.wpAllowDeleteCols)), _ AllowDeletingRows:=CBool(gvWksProtection(WksProtec tion.wpAllowDeleteRows)), _ AllowInsertingColumns:=CBool(gvWksProtection(WksPr otection.wpAllowInsertCols)), _ AllowInsertingRows:=CBool(gvWksProtection(WksProte ction.wpAllowInsertRows)), _ AllowInsertingHyperlinks:=CBool(gvWksProtection(Wk sProtection.wpAllowInsertHLinks)), _ AllowUsingPivotTables:=CBool(gvWksProtection(WksPr otection.wpAllowPivotTables)) Else .Protect Password:=PWRD, _ DrawingObjects:=CBool(gvWksProtection(WksProtectio n.wpDrawingObjects)), _ Contents:=CBool(gvWksProtection(WksProtection.wpCo ntents)), _ Scenarios:=CBool(gvWksProtection(WksProtection.wpS cenarios)), _ UserInterfaceOnly:=CBool(gvWksProtection(WksProtec tion.wpUserInterfaceOnly)) End If .EnableAutoFilter = CBool(gvWksProtection(WksProtection.wpEnableAutoFi lter)) .EnableOutlining = CBool(gvWksProtection(WksProtection.wpEnableOutlin ing)) .EnableSelection = CLng(gvWksProtection(WksProtection.wpEnableSelecti on)) End With 'Wks End Sub 'wksProtect() ...which you can also call directly from any routine. HTH Note that the main routine uses Enum elements which I also can access individually for each sheet by storing its protection settings in a defined name as shown here... Sub Set_EachWksProtection(Optional Wkb As Workbook) ' This applies sheet-specific protection as stored ' in the sheet's local scope defined name "uiProtect". Dim vSettings, Wks As Worksheet If Wkb Is Nothing Then Set Wkb = ActiveWorkbook Application.ScreenUpdating = False For Each Wks In Wkb.Worksheets vSettings = Empty On Error Resume Next vSettings = Wks.Names("uiProtect").RefersTo On Error GoTo 0 If Not (vSettings = Empty) Then vSettings = Application.Evaluate("'" & Wks.name & "'!uiProtect") gvWksProtection = Split(vSettings, ",") wksProtect Wks End If Next 'wks Application.ScreenUpdating = True: Set_DefaultWksProtection End Sub ...and the supporting routine follows. Sub Set_DefaultWksProtection() ' Use to reset to default params gvWksProtection = Split(gsDEF_WKS_PROTECTION, ",") End Sub The declaration defs used... Enum WksProtection wpDrawingObjects '0 wpContents '1 wpScenarios '2 wpUserInterfaceOnly '3 wpAllowFiltering '4 wpAllowFormatCols '5 wpAllowFormatRows '6 wpAllowFormatCells '7 wpAllowDeleteCols '8 wpAllowDeleteRows '9 wpAllowInsertCols '10 wpAllowInsertRows '11 wpAllowInsertHLinks '12 wpAllowPivotTables '13 wpEnableAutoFilter '14 wpEnableOutlining '15 wpEnableSelection '16: 0=xlNoRestrictions; 1=xlUnlockedCells; -4142=xlNoSelection End Enum Public gvWksProtection Const gsDEF_WKS_PROTECTION As String = "0,1,2,3,4,5,6,7,-8,-9,-10,-11,-12,-13,14,15,0" ...where the above Constant is the default used most commonly by my multi-sheet projects. The application concept is simple: positive numbers CBool as 'True', negative numbers as 'False'! The main routine processes all protection options I'd likely use in a project. The ResetProtection routine is used to re-apply non-persistent options at startup. As you can see, it gives me the option to go with the default settings or use sheet-specific settings when working with all sheets in a workbook. It also gives me the option to edit the 'active' settings stored in the gvWksProtection array and then apply it to a specific sheet (or sheets) or just the active sheet 'on-the-fly'. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Macro on All Sheets Q
Oops! I forgot to include the following routine...
Sub wksUnprotect(Optional Wks As Worksheet) If Wks Is Nothing Then Set Wks = ActiveSheet On Error Resume Next Wks.Unprotect PWRD End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Macro on All Sheets Q
I find one more routine from this module...
Sub SetProtection_AllSheets(Optional Wkb As Workbook, _ Optional bApply As Boolean = True) Dim Wks As Worksheet If Wkb Is Nothing Then Set Wkb = ActiveWorkbook Application.ScreenUpdating = False For Each Wks In Wkb.Worksheets If bApply Then ResetProtection Wks Else wksUnprotect Wks Next 'Wks Application.ScreenUpdating = True End Sub Note that I did not def the constant 'PWRD' in this modules declarations because it normally exists in my m_OpenClose module as a global, and so gets initiated by default when the project workbook opens. (I use Auto_Open/Auto_Close routines) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Macro on All Sheets Q
Sub looper()
Dim ws As Worksheet For Each ws In Sheets If ws.Name < "Sheet1" And ws.Name < "Sheet3" Then ws.Activate Call Original Macro End If Next End Sub Gord On Fri, 30 Aug 2013 12:40:13 -0700 (PDT), wrote: I have a macro that runs a routine on the active sheet, but how would I create a macro that will run this macro on 'all' sheets expect sheet1 & sheet4? I would prefer to create a new macro and call the original macro from within, so that I have the option to run individually |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Macro on All Sheets Q
Actually.., there were 2 more routines. This one lets you retrieve
protection settings for a specific sheet... Function Get_WksProtectionSettings$(Optional Wkb As Workbook, Optional Wks As Worksheet) ' This returns sheet-specific protection settings ' stored in the sheet's local scope defined name "uiProtect". Dim vSettings If Wkb Is Nothing Then Set Wkb = ActiveWorkbook If Wks Is Nothing Then Set Wks = ActiveSheet vSettings = Empty On Error Resume Next vSettings = Wks.Names("uiProtect").RefersTo On Error GoTo 0 If Not (vSettings = Empty) Then _ vSettings = Application.Evaluate("'" & Wks.name & "'!uiProtect") Get_WksProtectionSettings = vSettings End Function -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Macro on All Sheets Q
Just another way to do the same thing without hard-coding the
sheetnames inside the routine... Sub LoopSheets(ExcludedSheets$) Dim wks As Worksheet For each wks In ActiveWorkbook.Worksheets If Not InStr(1, ExcludedSheets, wks.Name) 0 Then _ wks.Activate: Call MainMacro Next 'wks End Sub ...where the sheets to be excluded from the process are a delimited string that can be assigned 'on-the-fly'! -OR- Sub MainMacro(Wks As Worksheet) With Wks '//do stuff End With 'Wks End Sub ...then revise LoopSheets as follows... Sub LoopSheets(ExcludedSheets$) Dim wks As Worksheet For each wks In ActiveWorkbook.Worksheets If Not InStr(1, ExcludedSheets, wks.Name) 0 Then _ Call MainMacro(wks) Next 'wks End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Macro on All Sheets Q
Keeping in the same context, but adding more flexibility, I've adapted
the following code example from procedures in my code archives. It demonstrates a means to include, or exclude, or both... Sub ProcessSheets(WksList$, Process&, Optional Order& = 1) ' Processes listed sheets as specified by Process ' If Process=2 then the default Order is 1 if omitted ' WksList: String value of sheetnames ' Process: Long value; 0=exclude, 1=include, 2=both ' Order: Long value; 0=exclude followed by include ' 1=include followed by exclude Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets Select Case Process Case 0: Call Macro0(wks) Case 1: Call Macro1(wks) Case 2 Select Case Order Case 0: Call Macro0(wks): Call Macro1(wks) Case 1: Call Macro1(wks): Call Macro0(wks) End Select 'Case Order End Select 'Case Process Next 'wks End Sub ...where the called procedure accepts a ref for the sheet to act on. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add sheets using macro | Excel Worksheet Functions | |||
sheets macro | Excel Programming | |||
"With Sheets" Issue - macro on one sheet to affect hidden rows on other sheets | Excel Programming | |||
Macro for filter on protected workbook that works for all sheets, no matter what sheets are named? | Excel Programming | |||
macro/new sheets | Excel Discussion (Misc queries) |