C
CB
Hi all,
I have an inventory spreadsheet for which I’m responsible for entering data
but I’m 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 Dalgleish’s code†of today).
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.
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 don’t 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. I’m not permitted to modify
anything unless I unprotect the worksheet. When I’m 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
I have an inventory spreadsheet for which I’m responsible for entering data
but I’m 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 Dalgleish’s code†of today).
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.
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 don’t 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. I’m not permitted to modify
anything unless I unprotect the worksheet. When I’m 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