View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
CB CB is offline
external usenet poster
 
Posts: 97
Default Autofilter, protected worksheets, Debra Dalgleish's code

Hi all,

Im having issues with code that Debra Dalgleish provided on her website at
<http://www.contextures.com/xlautofilter03.html#Protect. I dont have a lot
of coding experience (none in Excel; some in Access; C programming years ago)
so Im 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 Im 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