Autofilter, protected worksheets, Debra Dalgleish's code

[CB]

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

 

[JP]

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

 

[CB]

Hi JP,

Thanks for your prompt reply! Yes, the cell reference does make a
difference. In the last two work sheets I have a few rows above my data so my
column headings start on row 5 as opposed to row 2 in the first two tables.

After some further fiddling around, I finally got it to work. I changed the
range references to A2:B2 and A5:B5. I then had to go to each worksheet,
unprotect it, turn OFF autofilter then close the file and save changes. The
next time I opened the file, I only had drop-downs in the two cells specified
by the range.

Seems weird to me but I don't use Excel a lot and it works so who am I too
complain.

Chris