Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Code for multiple worksheets
I am using excel 2000
How can I make the following code, saved in the This Workbook module available to either all worksheets in the workbook or specifically an additional worksheet called "Commission Achievement" as well as the "Missing Data" worksheetin my workbook The code allows filterring on protected sheets Private Sub Workbook_Open() 'check for filter, turn on if none exists With Worksheets("Missing Data") If Not .AutoFilterMode Then .Range("A3").AutoFilter End If .EnableAutoFilter = True .Protect _ Contents:=True, UserInterfaceOnly:=True End With End Sub Thanks Paul |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code for multiple worksheets
One way:
Option Explicit Private Sub Workbook_Open() Dim iCtr As Long Dim WKSNames As Variant WKSNames = Array("Missing data", _ "Commission Achievement") For iCtr = LBound(WKSNames) To UBound(WKSNames) With Me.Worksheets(WKSNames(iCtr)) If Not .AutoFilterMode Then .Range("A3").AutoFilter End If .EnableAutoFilter = True .Protect Contents:=True, UserInterfaceOnly:=True End With Next iCtr End Sub If you really wanted all the worksheets in that workbook: Option Explicit Private Sub Workbook_Open() Dim wks As Worksheet For Each wks In Me.Worksheets With wks If Not .AutoFilterMode Then .Range("A3").AutoFilter End If .EnableAutoFilter = True .Protect Contents:=True, UserInterfaceOnly:=True End With Next wks End Sub Paul S wrote: I am using excel 2000 How can I make the following code, saved in the This Workbook module available to either all worksheets in the workbook or specifically an additional worksheet called "Commission Achievement" as well as the "Missing Data" worksheetin my workbook The code allows filterring on protected sheets Private Sub Workbook_Open() 'check for filter, turn on if none exists With Worksheets("Missing Data") If Not .AutoFilterMode Then Range("A3").AutoFilter End If EnableAutoFilter = True Protect _ Contents:=True, UserInterfaceOnly:=True End With End Sub Thanks Paul -- Paul S -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to know how to color code tabs on the Excel worksheets | Excel Discussion (Misc queries) | |||
Combine Worksheets - help with Code! | Excel Discussion (Misc queries) | |||
Delete Blank Rows Code - Multiple Worksheets - Multiple Documents | Excel Discussion (Misc queries) | |||
Need help on code - Macro on multiple worksheets | Excel Discussion (Misc queries) | |||
deleting selected worksheets using vb code | Excel Discussion (Misc queries) |