Hi Dan,
I apologize for taking so long to reply - not sure where the time went...
"dan dungan" wrote:
> Hi Chris,
>
> I'm not quite clear.
>
> >In a way it seems counter-intuitive to me to put it in
> >Privat Sub Workbook_Open() as that code is checking
> >if the sheet has autofilter on and if not
> >enabling autofilter then protecting the sheet.
>
> Which sheet are you trying to unprotect?
The four indicated in the code below.
>
> In your initial post you wrote:
>
> >I would like it if somehow I could enter the data into the
> >protected worksheet without having to unprotect each worksheet first.
>
> How many worksheets do you need to unprotect?
I have four worksheets that I need to be able to unprotect (which remained
protected for anyone else).
>
> >Ideally, I would like it if somehow the software would recognize
> >it is me and unprotect everything then reinforce protection
> >when I am done updating.
>
> How are the worksheets being protected, manually or by code?
> What version of Excel are you using?
> What type of Excel file are you using? xls? xlt?
>
The worksheets are being protected by code. I am using Excel 2003 and the
files are not templates (i.e., they are .xls not .xlt). I posted my code in
another message in this newgroup but I'm copying it below for your reference.
...........
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
.......................
> >Since I???m updating the workbook every week or two,
> >I don???t want to have to update my version, make a copy,
> >add in the code for protection and autofiltering,
> >then place that copy on the network.
>
> What stops you from keeping your copy on the network?
> I don't understand why you need to copy "your" version.
>
There are two supervisors that need to open the workbook, look at the data
and use autofilters. I do not want them to accidentally change/delete data so
I protected everything, which is sufficient for their use.
The thing though is that the workbook/worksheets are still protected when I
open the file. When I need to add data, I have to unprotect each worksheet
first. I can do that but I was looking assistance with code that would allow
me to bypass that step.
I had found code within the Excel newsgroups that worked for the user who
originally posted the message but it does not appear to work for me since I
still have to go through the process of unprotecting the worksheets.
Additionally, when I???m viewing the code and Run Sub/User Form I get
???Run-time error ???1004??? Application-defined or object-defined error.???
Hence, my original post.
Chris
> Dan
>