Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic protect/unprotect code through buttons and code?
I looked through the archives but have seen that there can be problems with
protecting/unprotecting sheets with vb so thought I would just ask about this one. I have buttons on some of my sheets. I'm ready to protect the sheets whereas up till now they remained unprotected. Apparently, we can unprotect sheet, run code, then protect sheet again. (I'm assuming buttons will still be clickable even though protected??) Also, we'll need a generic line of coding for protecting/unprotecting - i.e., without any sheet names and/or passwords - as that would be easiest to deal with as then I could simply copy those lines to all the current workbooks. It would mean adding a simple unprotect line after title of script and have code then execute rest of script - such as sort, etc. - and then protect the sheet before script finishes. Also, people have posted about problems with passwords so we won't need any passwords. And the other problem I saw is that code might not work if sheet protected manually, etc. So it was recommended in that particular post to use an IF statement. I haven't been able to find an example of code that will do these 2 things so begging indulgence of group to help with this one. This is final step in completing all the workbooks I made here at this job 1 1/2 yrs ago. They've been unprotected all this time. Hopefully I can close them up yet still retain full functionality with all the buttons/code! <g Thank you!! :oD |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic protect/unprotect code through buttons and code?
Apparently, we can unprotect sheet, run code, then protect sheet again.
Yes. (I'm assuming buttons will still be clickable even though protected??) Yes. You may need to change the objects protection if it is locked. Try it to find out. we'll need a generic line of coding for protecting/unprotecting - i.e., without any sheet names and/or passwords Use Activesheet. And the other problem I saw is that code might not work if sheet protected manually, etc. I've never had that problem.... which does not mean that it is not a problem. I haven't been able to find an example of code that will do these 2 things so begging indulgence of group to help with this one. ActiveSheet.Unprotect 'place at the beginning of the code ActiveSheet.Protect 'place in the exit routine so it is executed even if the error routine is entered -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "StargateFanFromWork" wrote in message ... I looked through the archives but have seen that there can be problems with protecting/unprotecting sheets with vb so thought I would just ask about this one. I have buttons on some of my sheets. I'm ready to protect the sheets whereas up till now they remained unprotected. Apparently, we can unprotect sheet, run code, then protect sheet again. (I'm assuming buttons will still be clickable even though protected??) Also, we'll need a generic line of coding for protecting/unprotecting - i.e., without any sheet names and/or passwords - as that would be easiest to deal with as then I could simply copy those lines to all the current workbooks. It would mean adding a simple unprotect line after title of script and have code then execute rest of script - such as sort, etc. - and then protect the sheet before script finishes. Also, people have posted about problems with passwords so we won't need any passwords. And the other problem I saw is that code might not work if sheet protected manually, etc. So it was recommended in that particular post to use an IF statement. I haven't been able to find an example of code that will do these 2 things so begging indulgence of group to help with this one. This is final step in completing all the workbooks I made here at this job 1 1/2 yrs ago. They've been unprotected all this time. Hopefully I can close them up yet still retain full functionality with all the buttons/code! <g Thank you!! :oD |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic protect/unprotect code through buttons and code?
StargateFanFromWork wrote: I looked through the archives but have seen that there can be problems with protecting/unprotecting sheets with vb so thought I would just ask about this one. I have buttons on some of my sheets. I'm ready to protect the sheets whereas up till now they remained unprotected. Apparently, we can unprotect sheet, run code, then protect sheet again. (I'm assuming buttons will still be clickable even though protected??) Also, we'll need a generic line of coding for protecting/unprotecting - i.e., without any sheet names and/or passwords - as that would be easiest to deal with as then I could simply copy those lines to all the current workbooks. It would mean adding a simple unprotect line after title of script and have code then execute rest of script - such as sort, etc. - and then protect the sheet before script finishes. Also, people have posted about problems with passwords so we won't need any passwords. And the other problem I saw is that code might not work if sheet protected manually, etc. So it was recommended in that particular post to use an IF statement. I haven't been able to find an example of code that will do these 2 things so begging indulgence of group to help with this one. This is final step in completing all the workbooks I made here at this job 1 1/2 yrs ago. They've been unprotected all this time. Hopefully I can close them up yet still retain full functionality with all the buttons/code! <g Thank you!! :oD Have only been working with VBA for less than a year, this time (did a little years ago, but not much). But I have been working with password protected sheets for almost that long with no problems whatsoever. I've just coded the macros to unprotect and the protect, using the appropriate password. Hope this helps in your world. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic protect/unprotect code through buttons and code?
On Fri, 30 Dec 2005 14:13:48 -0600, "AnExpertNovice"
wrote: Apparently, we can unprotect sheet, run code, then protect sheet again. Yes. (I'm assuming buttons will still be clickable even though protected??) Yes. You may need to change the objects protection if it is locked. Try it to find out. we'll need a generic line of coding for protecting/unprotecting - i.e., without any sheet names and/or passwords Use Activesheet. And the other problem I saw is that code might not work if sheet protected manually, etc. I've never had that problem.... which does not mean that it is not a problem. I haven't been able to find an example of code that will do these 2 things so begging indulgence of group to help with this one. ActiveSheet.Unprotect 'place at the beginning of the code ActiveSheet.Protect 'place in the exit routine so it is executed even if the error routine is entered <g Okay, well, that was darned easy!! I don't know why I had such trouble before, all those months ago!! I guess I was just too, too new at this <g. This is super fantastic! Next week I can start locking up every single workbook I made for the team. Phew, that's going to save a lot of heartache. They'll be able to sort and everything, but they'll stop "corrupting" the "interface" <lol. Thanks bunches!! :oD |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic protect/unprotect code through buttons and code?
On 30 Dec 2005 12:23:34 -0800, "davegb" wrote:
StargateFanFromWork wrote: I looked through the archives but have seen that there can be problems with protecting/unprotecting sheets with vb so thought I would just ask about this one. I have buttons on some of my sheets. I'm ready to protect the sheets whereas up till now they remained unprotected. Apparently, we can unprotect sheet, run code, then protect sheet again. (I'm assuming buttons will still be clickable even though protected??) Also, we'll need a generic line of coding for protecting/unprotecting - i.e., without any sheet names and/or passwords - as that would be easiest to deal with as then I could simply copy those lines to all the current workbooks. It would mean adding a simple unprotect line after title of script and have code then execute rest of script - such as sort, etc. - and then protect the sheet before script finishes. Also, people have posted about problems with passwords so we won't need any passwords. And the other problem I saw is that code might not work if sheet protected manually, etc. So it was recommended in that particular post to use an IF statement. I haven't been able to find an example of code that will do these 2 things so begging indulgence of group to help with this one. This is final step in completing all the workbooks I made here at this job 1 1/2 yrs ago. They've been unprotected all this time. Hopefully I can close them up yet still retain full functionality with all the buttons/code! <g Thank you!! :oD Have only been working with VBA for less than a year, this time (did a little years ago, but not much). But I have been working with password protected sheets for almost that long with no problems whatsoever. I've just coded the macros to unprotect and the protect, using the appropriate password. Hope this helps in your world. Yes, it did work well <fingers crossed. I didn't use a password but so far, so good. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to protect/unprotect a sheet using a macro with password | Excel Discussion (Misc queries) | |||
Unprotect Code Module in Code | Excel Discussion (Misc queries) | |||
Please help!!! Using code to password-protect and unprotect... | Excel Programming | |||
VBA code - protect and unprotect a sheet | Excel Programming |