Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vital: Protecting sheets so macros/buttons still work?
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vital: Protecting sheets so macros/buttons still work?
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 "StargateFanFromWork" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vital: Protecting sheets so macros/buttons still work?
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, "StargateFanFromWork" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vital: Protecting sheets so macros/buttons still work?
Sorry, hit Send prematurely!
Try wrapping your code in Sheets("Sheet1").Unprotect ("YourPassword") Your Code Sheets("Sheet1").Protect ("YourPassword") Regards, "StargateFanFromWork" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vital: Protecting sheets so macros/buttons still work?
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vital: Protecting sheets so macros/buttons still work?
On Wed, 30 Jun 2004 16:19:34 -0400, "StargateFanFromWork"
wrote: "Marcotte A" wrote in message ... [snip] This can be handled as suggested: Sub YourMacro() ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPa ssword") 'your code ThisWorkbook.Worksheets("Sheet1).Protect("YourPass word") 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vital: Protecting sheets so macros/buttons still work?
On Thu, 1 Jul 2004 08:20:01 -0700, "Marcotte A"
wrote: "StargateFan" wrote: On Wed, 30 Jun 2004 16:19:34 -0400, "StargateFanFromWork" 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vital: Protecting sheets so macros/buttons still work?
"Marcotte A" wrote in message
... [snip] This can be handled as suggested: Sub YourMacro() ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPa ssword") 'your code ThisWorkbook.Worksheets("Sheet1).Protect("YourPass word") 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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vital: Protecting sheets so macros/buttons still work?
On Wed, 30 Jun 2004 10:38:24 -0400, "StargateFanFromWork"
wrote: 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! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vital: Protecting sheets so macros/buttons still work?
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 "StargateFanFromWork" wrote in message ... "Marcotte A" wrote in message ... [snip] This can be handled as suggested: Sub YourMacro() ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPa ssword") 'your code ThisWorkbook.Worksheets("Sheet1).Protect("YourPass word") 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! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vital: Protecting sheets so macros/buttons still work?
On Sat, 3 Jul 2004 18:58:57 +0800, "JulieD"
wrote: 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! "StargateFanFromWork" wrote in message ... "Marcotte A" wrote in message ... [snip] This can be handled as suggested: Sub YourMacro() ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPa ssword") 'your code ThisWorkbook.Worksheets("Sheet1).Protect("YourPass word") 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! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vital: Protecting sheets so macros/buttons still work?
On Sat, 03 Jul 2004 00:13:23 -0400, StargateFan
wrote: On Wed, 30 Jun 2004 10:38:24 -0400, "StargateFanFromWork" wrote: [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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vital: Protecting sheets so macros/buttons still work?
you're welcome
"StargateFan" wrote in message ... On Sat, 3 Jul 2004 18:58:57 +0800, "JulieD" wrote: 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! "StargateFanFromWork" wrote in message ... "Marcotte A" wrote in message ... [snip] This can be handled as suggested: Sub YourMacro() ThisWorkbook.Worksheets("Sheet1).Unprotect("YourPa ssword") 'your code ThisWorkbook.Worksheets("Sheet1).Protect("YourPass word") 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! |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vital: Protecting sheets so macros/buttons still work?
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! "StargateFan" wrote in message ... On Sat, 3 Jul 2004 18:58:57 +0800, "JulieD" wrote: [snip] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protecting all but command buttons | Excel Discussion (Misc queries) | |||
Protecting My Macros | Excel Worksheet Functions | |||
Protecting Macros -is it possible | Excel Programming | |||
Macros assigned to buttons do not work when copied to CD | Excel Programming |