Hi all,

I have an inventory spreadsheet for which Im responsible for entering data
but Im placing a COPY on the network for two managers to view as they need
to keep an eye on quantities.

The COPY on the network is protected (the worksheets and the workbook) and
only allows them to autofilter data (per my post €œAutofilter, protected
worksheet, Debra Dalgleishs code€� of today).

Since Im updating the workbook every week or two, I dont 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.

I would like it if somehow I could enter the data into the protected
worksheet without having to unprotect each worksheet first. 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.

Is this possible?

I searched the Excel forum and found a post on 10/4/06 in response by JE
McGimsey <http://tinyurl.com/46jtvo to a post named €œWorksheet protection€�
in which he gives the following code.

Public Sub UnProtectAll()

Const sPWORD As String = "myPW"
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect sPWORD
Next ws
End Sub

The code worked for the original poster.

When I compile the code (which I placed in ThisWorkbook), I dont get an
error but it does not seem that this code works for me in that nothing
happens when I close the file then reopen. Im not permitted to modify
anything unless I unprotect the worksheet. When Im viewing the code and Run
Sub/User Form I get €œRun-time error €˜1004 Application-defined or
object-defined error.€�

What am I missing?

Thanks for any and all help!

Chris

Hi Chris,

How are you calling the sub?

I think it needs to be in something like:

Private Sub Workbook_Open()

dan

Hi Dan,

I'm using the code exactly as I found it (other than a different password),
i.e.,

Public Sub UnProtectAll()

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.

Clearly, I don't understand how the code to unprotectall works. I'd
appreciate any clarity you could provide.

Chris

"dan dungan" wrote:

Hi Chris,

How are you calling the sub?

I think it needs to be in something like:

Private Sub Workbook_Open()

dan

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?

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?

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?

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.

Dan

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 Im updating the workbook every week or two,
I dont 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 Im 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