Vital: Protecting sheets so macros/buttons still work?

[StargateFanFromWork]

This is a big one, it's become extremely vital. Over last 3 weeks I have
done a lot of work writing up several workbooks that contain numerous
buttons with macros assigned to them. One false step or accidental delete
and those buttons are gone! But when I protect the sheet, all the buttons
return error codes.

Can someone pls direct me to a webpage tutorial that explains how to protect
sheets so that the buttons will still work? I understand how to lock cells
and such, that I've been doing for years, it's protecting the sheet as per
normal but allowing the buttons full functionality.

Thank you!

 

[JulieD]

Hi

do you mean that when you press the button it returns an error as you're
trying to do something you can't do when the sheet is protected? if so how
about incorporating in your macros the unprotecting of the sheet at the
start & the reprotecting of the sheet at the end of the macro?

Cheers
JulieD

 

[Alan]

What are the error codes? Protecting a sheet shouldn't affect the execution
of a macro It may be as simple as the buttons attempting to alter the value
of a protected cell.
Regards,

 

[Alan]

Sorry, hit Send prematurely!
Try wrapping your code in

Sheets("Sheet1").Unprotect ("YourPassword")
Your Code
Sheets("Sheet1").Protect ("YourPassword")

Regards,

 

[Gary]

Sorry, hit Send prematurely!
Try wrapping your code in

Sheets("Sheet1").Unprotect ("YourPassword")
Your Code
Sheets("Sheet1").Protect ("YourPassword")

I would also suggest using unprotect in the first few lines of the macro
and then protect command in thre last few lines. Thats how I do it on my
sheets

 

[StargateFanFromWork]

What kind of errors are you getting? Do the buttons change cells on the

sheet? Adding a .Unprotect statement at the beginning of the buttons code
(and a .Protect at the end) may solve part of your problem.

That sounds very plausible. Now that my sheets have more macros on them,
it's interesting to see the slightly different errors that come up. I just
did a global lock of all the cells in the sheet. I unlocked only the ones
that the user will type data into. Then I protected the sheet _without_ a
password (not necessary).

This is a breakdown of the errors for the 5 buttons on this sheet. The
biggest culprit seems to be anything with a sort:

Macro going to bottom of column, no error. Works fine.
**********************************************************************
Button with sort macro = error:

Run-time error '1004':

Sort method of Range class failed
**********************************************************************
Button with insert row macro = error:

Run-time error '1004':

You cannot use this command on a protected sheet. To unprotect the sheet,
use the Unprotect Sheet command (Tools menu, Protection submenu). You may
be prompted for a password.
**********************************************************************
Button with a macro that takes us to the top of the sheet, no error. Works
fine.
**********************************************************************
Print button which sorts before bringing up print dialogue box = error (same
sort error, it seems):

Run-time error '1004':

Sort method of Range class failed
**********************************************************************

Thank you!


p.s., this is the average type of sort macro used on all the workbooks I've
created, with slight variations for cell names:

"Sub SortByFILENAME()
'
' Macro recorded 6/25/2004 by ...
'

'
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub"

The code you speak of I imagine would go on a line just above the
"Selection.SortKey..." to unprotect and then just above "End Sub" to protect
again??

 

[Guest]

**********************************************************************

Button with sort macro = error:

Run-time error '1004':

Sort method of Range class failed
**********************************************************************
Button with insert row macro = error:

Run-time error '1004':

You cannot use this command on a protected sheet. To unprotect the sheet,
use the Unprotect Sheet command (Tools menu, Protection submenu). You may
be prompted for a password.
**********************************************************************

This can be handled as suggested:
Sub YourMacro()
ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPassword")
'your code
ThisWorkbook.Worksheets("Sheet1).Protect("YourPassword")
End Sub

If you have the workbook protected instead then:
ThisWorkbook.Protect etc.

**********************************************************************
Print button which sorts before bringing up print dialogue box = error (same
sort error, it seems):

Run-time error '1004':

Sort method of Range class failed
**********************************************************************

I believe this and the other sort macro can be fixed in the same way. Not 100% sure, but since the Sort menu option is grayed out when sheet protection is on, I would guess that unprotecting in the code will solve your problems. Good Luck.

Thank you!


p.s., this is the average type of sort macro used on all the workbooks I've
created, with slight variations for cell names:

"Sub SortByFILENAME()
'
' Macro recorded 6/25/2004 by ...
'

'
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub"

The code you speak of I imagine would go on a line just above the
"Selection.SortKey..." to unprotect and then just above "End Sub" to protect
again??

Correct.

 

[StargateFanFromWork]

[snip]

This can be handled as suggested:
Sub YourMacro()
ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPassword")
'your code
ThisWorkbook.Worksheets("Sheet1).Protect("YourPassword")
End Sub

If you have the workbook protected instead then:
ThisWorkbook.Protect etc.


I believe this and the other sort macro can be fixed in the same way. Not

100% sure, but since the Sort menu option is grayed out when sheet
protection is on, I would guess that unprotecting in the code will solve
your problems. Good Luck.

Correct.

Phew, okay, I'm feeling a little shaky, so if I may, I'm copying the above
macro with close to what I believe unprotect/protect code might be? p.s.,
to reiterate, I don't use passwords, it's not necessary; and, secondly, can
a "generic" worksheet name be used somehow? I have over a dozen sheets in
various workbooks to add the unprotect/protect code and it would be so much
easier to not have to name the sheets. This way, it'll be much easier to
quickly secure these documents from accidental modification of the layouts
and buttons.



Sub SortByFILENAME()
'
' Macro recorded 6/25/2004 by ...
'

'
ThisWorkbook.Worksheets().Unprotect()
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets().Protect()
End Sub

Thanks so much!

 

[StargateFan]

[snip]

This can be handled as suggested:
Sub YourMacro()
ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPassword")
'your code
ThisWorkbook.Worksheets("Sheet1).Protect("YourPassword")
End Sub

If you have the workbook protected instead then:
ThisWorkbook.Protect etc.

[snip]

Phew, okay, I'm feeling a little shaky, so if I may, I'm copying the above
macro with close to what I believe unprotect/protect code might be? p.s.,
to reiterate, I don't use passwords, it's not necessary; and, secondly, can
a "generic" worksheet name be used somehow? I have over a dozen sheets in
various workbooks to add the unprotect/protect code and it would be so much
easier to not have to name the sheets. This way, it'll be much easier to
quickly secure these documents from accidental modification of the layouts
and buttons.

[snip]

I'm not sure of syntax, so re-postiong.

Sub SortByFILENAME()
'
' Macro recorded 6/25/2004 by ...
'

'
ThisWorkbook.Worksheets().Unprotect()
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets().Protect()
End Sub

************
I don't use passwords so the part where one enters a password, is that
code necessary? Also, all the sheets, as I mentioned above, have
different tab names. Is there a "generic" type of code for the sheet
name that will work?

Thanks.

 

[StargateFan]

Phew, okay, I'm feeling a little shaky, so if I may, I'm copying the above
macro with close to what I believe unprotect/protect code might be? p.s.,
to reiterate, I don't use passwords, it's not necessary; and, secondly, can
a "generic" worksheet name be used somehow? I have over a dozen sheets in
various workbooks to add the unprotect/protect code and it would be so much
easier to not have to name the sheets. This way, it'll be much easier to
quickly secure these documents from accidental modification of the layouts
and buttons.

[snip]

I'm not sure of syntax, so re-postiong.

Sub SortByFILENAME()
'
' Macro recorded 6/25/2004 by ...
'

'
ThisWorkbook.Worksheets().Unprotect()
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets().Protect()
End Sub

************
I don't use passwords so the part where one enters a password, is that
code necessary? Also, all the sheets, as I mentioned above, have
different tab names. Is there a "generic" type of code for the sheet
name that will work?

Thanks.

You don't nee the "()" after Protect and Unprotect. As to doing multiple sheets, try:
Sub Sort()
Dim wks as Worksheet
For Each wks in ThisWorkbook
wks.Unprotect
Next wks

'your code

For Each wks in ThisWorkbook
wks.Protect
Next wks
End Sub

I'm sorry, I've confused the issue again. Very frustrating not
knowing terminology.

I only mentioned all the different sheets in the different workbooks
because I'm hoping to use the same code for all of them, copy/paste in
other words. I _don't_ need to reference them at all in the script.
The code above would be perfect but I'm just not sure of the syntax at
all. The original code calls for using an actual sheet name which is
no good for me. If the user changes the sheet name, I want the code
to still work yet I don't want to limit the user to not being able to
change the sheet name, if indeed such a thing would be possible.
Again, I _don't_ want to restrict this.

But, again, the code _does_not_ need to reference more than one sheet.
I don't have the documents here to test, I'l test at work later today
but here is the modified code:



Sub SortByFILENAME()
'
' Macro recorded 6/25/2004 by ...
'

'
ThisWorkbook.Worksheets.Unprotect
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets.Protect

End Sub


Will this _really_ work on any workbook?

The only thing that would need changing for one or two sheets is the
"B2" reference, of course.. Most are B2, but I believe one or two,
due to an extra column, would be C2.

It just seems odd that the () aren't needed, but hey, what do I know??
<g>

Thanks!

 

[StargateFanFromWork]

[snip]

This can be handled as suggested:
Sub YourMacro()
ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPassword")
'your code
ThisWorkbook.Worksheets("Sheet1).Protect("YourPassword")
End Sub

If you have the workbook protected instead then:
ThisWorkbook.Protect etc.

I knew it. This isn't working. I get a runtime error of 9, this time
around, "Subscript out of range".

Here's the coding, fiddle with many times:


ThisWorkbook.Worksheets("").Unprotect ("")
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets("").Protect ("")


Why did I change it from the above? Because I'm hoping that I can just
copy/paste the code into the 20 other sort macros in all the workbooks I've
created. Also, it'll make portability much more feasible. I'll probably be
re-using this code, again and again. I'd like to leave the worksheet name
out of things always and I never use passwords. I'm a contract worker and I
need to know that they can go in and modify anything if they need to down
the road after I'm gone.

I tried numerous variations on the above Unprotect and Protect syntax but
often got the red right away.

Is there a command builder in Excel that can help? One that can give better
and more precise help than trying to do a google search. Googling gives no
results anyway, btw (I've tried in the past). There's got to be a way for us
to much more easily look up code to do something and to get the variations
in the syntax right very quickly. That would save all of us because I
wouldn't pester the groups so much! <lol>

Anyway, that would be appreciated but also, a more generic code that works
for the above <sigh>. This is the only issue standing in the way of
protecting and locking up all these sheets.

Thanks so much! I really appreciate all the help!

 

[StargateFan]

This is a big one, it's become extremely vital. Over last 3 weeks I have
done a lot of work writing up several workbooks that contain numerous
buttons with macros assigned to them. One false step or accidental delete
and those buttons are gone! But when I protect the sheet, all the buttons
return error codes.

Can someone pls direct me to a webpage tutorial that explains how to protect
sheets so that the buttons will still work? I understand how to lock cells
and such, that I've been doing for years, it's protecting the sheet as per
normal but allowing the buttons full functionality.

Thank you!

Thank you so much to Dave Peterson who gave code that worked great in
my test file here at home. Here is the syntax, the only thing that
needs modifying is the macro name and the code one puts in that the
macro deals with, of course <g>:

Sub MacroName()
'
With ActiveSheet
.Unprotect
(code goes here)
End With
End Sub



The above allowed me to apply _one_ unprotect/protect lines of code to
the macros regardless of sheet names. So in this test file, where I
had 10 sheets, 2 macros did the job! If I had not had a generic type
of code - in other examples where the sheet name had to be entered, in
other words - 20 different macros would have been required! Not a
pleasant prospect.

Thank you so much to Dave for this! This was the last major hurdle
<crossing fingers> to fix on all the workbooks. The remaining two
weeks will allow me to test the files fully as I continue doing my job
which requires using these files on a daily basis.

Cheers!

 

[JulieD]

Hi

you can use
ActiveSheet.Protect
and
ActiveSheet.Unprotect
which will protect (unprotect) the currently active sheet without having to
specify the sheet name

which i think is what you're after.

Regards
JulieD

[snip]

This can be handled as suggested:
Sub YourMacro()
ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPassword")
'your code
ThisWorkbook.Worksheets("Sheet1).Protect("YourPassword")
End Sub

If you have the workbook protected instead then:
ThisWorkbook.Protect etc.

I knew it. This isn't working. I get a runtime error of 9, this time
around, "Subscript out of range".

Here's the coding, fiddle with many times:


ThisWorkbook.Worksheets("").Unprotect ("")
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets("").Protect ("")


Why did I change it from the above? Because I'm hoping that I can just
copy/paste the code into the 20 other sort macros in all the workbooks I've
created. Also, it'll make portability much more feasible. I'll probably be
re-using this code, again and again. I'd like to leave the worksheet name
out of things always and I never use passwords. I'm a contract worker and I
need to know that they can go in and modify anything if they need to down
the road after I'm gone.

I tried numerous variations on the above Unprotect and Protect syntax but
often got the red right away.

Is there a command builder in Excel that can help? One that can give better
and more precise help than trying to do a google search. Googling gives no
results anyway, btw (I've tried in the past). There's got to be a way for us
to much more easily look up code to do something and to get the variations
in the syntax right very quickly. That would save all of us because I
wouldn't pester the groups so much! <lol>

Anyway, that would be appreciated but also, a more generic code that works
for the above <sigh>. This is the only issue standing in the way of
protecting and locking up all these sheets.

Thanks so much! I really appreciate all the help!

 

[StargateFan]

Hi

you can use
ActiveSheet.Protect
and
ActiveSheet.Unprotect
which will protect (unprotect) the currently active sheet without having to
specify the sheet name

which i think is what you're after.

Regards
JulieD

Thank you!

[snip]

This can be handled as suggested:
Sub YourMacro()
ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPassword")
'your code
ThisWorkbook.Worksheets("Sheet1).Protect("YourPassword")
End Sub

If you have the workbook protected instead then:
ThisWorkbook.Protect etc.

I knew it. This isn't working. I get a runtime error of 9, this time
around, "Subscript out of range".

Here's the coding, fiddle with many times:


ThisWorkbook.Worksheets("").Unprotect ("")
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets("").Protect ("")


Why did I change it from the above? Because I'm hoping that I can just
copy/paste the code into the 20 other sort macros in all the workbooks I've
created. Also, it'll make portability much more feasible. I'll probably be
re-using this code, again and again. I'd like to leave the worksheet name
out of things always and I never use passwords. I'm a contract worker and I
need to know that they can go in and modify anything if they need to down
the road after I'm gone.

I tried numerous variations on the above Unprotect and Protect syntax but
often got the red right away.

Is there a command builder in Excel that can help? One that can give better
and more precise help than trying to do a google search. Googling gives no
results anyway, btw (I've tried in the past). There's got to be a way for us
to much more easily look up code to do something and to get the variations
in the syntax right very quickly. That would save all of us because I
wouldn't pester the groups so much! <lol>

Anyway, that would be appreciated but also, a more generic code that works
for the above <sigh>. This is the only issue standing in the way of
protecting and locking up all these sheets.

Thanks so much! I really appreciate all the help!

 

[StargateFan]

On Wed, 30 Jun 2004 10:38:24 -0400, "StargateFanFromWork"

[snip]

Sub MacroName()
'
With ActiveSheet
.Unprotect
(code goes here)
End With
End Sub

WOOPS, missing line. The code above s/b:





Sub MacroName()
'
'
With ActiveSheet
.Unprotect
(code goes here)
.Protect
End With
End Sub





Sorry about that! I accidentally deleted the ".Protect" line when
modifying existing code to get the above to share with the group.
Glad I noticed it, though. <g>

 

[JulieD]

you're welcome

Hi

you can use
ActiveSheet.Protect
and
ActiveSheet.Unprotect
which will protect (unprotect) the currently active sheet without having to
specify the sheet name

which i think is what you're after.

Regards
JulieD

Thank you!

[snip]

This can be handled as suggested:
Sub YourMacro()
ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPassword")
'your code
ThisWorkbook.Worksheets("Sheet1).Protect("YourPassword")
End Sub

If you have the workbook protected instead then:
ThisWorkbook.Protect etc.

I knew it. This isn't working. I get a runtime error of 9, this time
around, "Subscript out of range".

Here's the coding, fiddle with many times:


ThisWorkbook.Worksheets("").Unprotect ("")
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Worksheets("").Protect ("")


Why did I change it from the above? Because I'm hoping that I can just
copy/paste the code into the 20 other sort macros in all the workbooks I've
created. Also, it'll make portability much more feasible. I'll

probably
be

re-using this code, again and again. I'd like to leave the worksheet name
out of things always and I never use passwords. I'm a contract worker

and
I

need to know that they can go in and modify anything if they need to down
the road after I'm gone.

I tried numerous variations on the above Unprotect and Protect syntax but
often got the red right away.

Is there a command builder in Excel that can help? One that can give better
and more precise help than trying to do a google search. Googling

gives
no

results anyway, btw (I've tried in the past). There's got to be a way

for
us

to much more easily look up code to do something and to get the variations
in the syntax right very quickly. That would save all of us because I
wouldn't pester the groups so much! <lol>

Anyway, that would be appreciated but also, a more generic code that works
for the above <sigh>. This is the only issue standing in the way of
protecting and locking up all these sheets.

Thanks so much! I really appreciate all the help!

 

[StargateFanFromWork]

Thanks so much to everyone who helped with this re this thread! I am
indebted tremendously to anyone who responded and helped out.

As some might be aware, I've been at this for a couple of weeks due to
pressures from work. My goal was to hopefully protect all the many
workbooks I had to create here so that users after me wouldn't corrupt the
docts. re the macro functionality.

Despite tremendous efforts on everyone's part, I've come to the conclusion
that this can't be done; at least, not by a power user newbie such as myself
under the time constraints, esp., that are also involved.

I got a lot of syntax from this group and it all works quite well. But when
a sheet is protected, a lot of functions are locked out. There was other
syntax that took care of the major stuff re unprotecting then protecting
again, but as I've been working with a protected test sheet today, I found
that many, many other little functions don't work and are greyed out. So
much so that I see only a much more advanced programmer being able to handle
them all. I have neither the time nor the expertise, time because it takes
someone like me much longer to come up with solutions than an expert. My
supervisor is away today and it's relatively quiet, so I was able to devote
a lot of time to so-call finish up the workbooks.

What did it was when I started trying to work around the locked out items,
yes, but the clincher was no UNDO. With specific commands, it's easy enough
to go to the groups via google and look things up in the archives but it
wasn't till I was looking at all the msgs re UNDO that I became hopelessly
discouraged and knew that it was time to call it a day. What sounded the
death knell on protection was that undo does not work on
programmatically-deleted items and that a complex vba script would be needed
to remember the data before any deletions could be restored.

So that was the straw that broke the camel's back.

A MAJOR item for the Excel wishlist - though have to say the list is quite
small as Excel is super fantastic! - a way to protect BUTTONS only that
doesn't stop everything else from working as when one has to protect a
workbook or worksheet but that also doesn't stop the buttons from working
<g>.

So, here's the conclusion to the whole saga - I will in all likelihood have
to leave the sheets unprotected since the obstacles are much greater to the
user if I don't. And the knowledge that within very little time, they'll
corrupt the document to one degree or another. They'll always be able to
use the books manually, it's just that I'm sure somewhere down the road,
someone is going to delete a button or two and will lose a lot of
functionality.

Ah well. I certainly gave it 110% effort and that's all that counts.

Thanks everyone, once again. Above and beyond is the help I've recvd. This
contract was unique. I doubt I'll ever have to do this type of thing again
re Excel. But I do also take away valuable knowledge thanks to the Excel
groups.

Cheers!

[snip]