In the last two code sections, the range is B5, not B2, does that make
a difference?

You could make the code shorter by using a For Each Loop, for example

Dim ws As Excel.Worksheet

For Each ws in Worksheets
If Not .AutoFilterMode Then
.Range("B2").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="myPW", _
Contents:=True, UserInterfaceOnly:=True
Next ws


--JP

On Sep 18, 3:59*pm, CB wrote:
Hi all,

I’m having issues with code that Debra Dalgleish provided on her website at
<http://www.contextures.com/xlautofilter03.html#Protect. I don’t have a lot
of coding experience (none in Excel; some in Access; C programming years ago)
so I’m including my code below in case someone can spot errors.

Two questions:
1) The code works perfectly for the first two worksheets (which are
identical except for data). Drop-downs appear in the range A2:H2. I only need
it in B2 but I’m okay with that as that my data are in those columns. The
problem lies in the last two worksheets (which are different from the first 2
but identical to each other except for data). On those worksheets, only one
drop-down appears in B1, which is of no use to me! *It makes no sense
whatsoever as I copied/pasted the code and simply changed worksheet names and
ranges.

2) Something is niggling in the back of my brain telling me that the code is
too long. Is there a more efficient way to accomplish the same thing as the
four WITH statements?

Thanks for any and all help!

Chris
……………………………………………………………..

Private Sub Workbook_Open()

'check for filter, turn on if none exists

With Worksheets("Depot_Inventory_Serialized")
* If Not .AutoFilterMode Then
* * .Range("B2").AutoFilter
* End If
* .EnableAutoFilter = True
* .Protect Password:="myPW", _
* Contents:=True, UserInterfaceOnly:=True
End With

With Worksheets("Warehouse_Inventory_Serialized")
* If Not .AutoFilterMode Then
* * .Range("B2").AutoFilter
* End If
* .EnableAutoFilter = True
* .Protect Password:="myPW", _
* Contents:=True, UserInterfaceOnly:=True
End With

With Worksheets("Depot_Inventory_non-Serial")
* If Not .AutoFilterMode Then
* * .Range("B5").AutoFilter
* End If
* .EnableAutoFilter = True
* .Protect Password:="myPW", _
* Contents:=True, UserInterfaceOnly:=True
End With

With Worksheets("Warehouse_Inventory_non-Serial")
* If Not .AutoFilterMode Then
* * .Range("B5").AutoFilter
* End If
* .EnableAutoFilter = True
* .Protect Password:="myPW", _
* Contents:=True, UserInterfaceOnly:=True

End With

End Sub