Macro to hide/unhide in protected sheet produces error code
- Thread starter HombreMan
- Start date
Hi HombreMan,
I assume:
1)you have two buttons, one for hide and another for unhide.
2)you have two subroutines, one for hide and another for unhide; and you have assigned these macros separately to hide and unhide buttons, respectively
In a module, paste the following two macro:
Sub ProtectAll()
'Prptect the worksheets
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Protect
Next sh
End Sub
Sub UnProtectAll()
'Unprotect the worksheets
Dim sh As Worksheet
Dim myPassword As String
For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect
Next sh
End Sub
Now in you hide/unhide macro, rearrange the code as follows:
Sub YourHideMacro
call UnProtectAll
"" your hide code will then go here"
Call ProtectAll
End Sub
Sub YourUnHideMacro
call UnProtectAll
"" your unhide code will then go here"
Call ProtectAll
End Sub
Hope this helps.
However, as suggested by SirJB7, I would also request you to elaborate your problem bit more clearly in case the above instruction does not help you..
Regards,
Kaushik
I assume:
1)you have two buttons, one for hide and another for unhide.
2)you have two subroutines, one for hide and another for unhide; and you have assigned these macros separately to hide and unhide buttons, respectively
In a module, paste the following two macro:
Sub ProtectAll()
'Prptect the worksheets
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Protect
Next sh
End Sub
Sub UnProtectAll()
'Unprotect the worksheets
Dim sh As Worksheet
Dim myPassword As String
For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect
Next sh
End Sub
Now in you hide/unhide macro, rearrange the code as follows:
Sub YourHideMacro
call UnProtectAll
"" your hide code will then go here"
Call ProtectAll
End Sub
Sub YourUnHideMacro
call UnProtectAll
"" your unhide code will then go here"
Call ProtectAll
End Sub
Hope this helps.
However, as suggested by SirJB7, I would also request you to elaborate your problem bit more clearly in case the above instruction does not help you..
Regards,
Kaushik
Pablo Sir,
Thank you for your kind words..
Actually it was my blind guess...
I just thought that we should first unprotect the sheet(s), then perform hide/unhide action and finally protect the sheet(s) again to avoid any potential error...
But no idea.....this is what all about 'HombreMan' is looking for
Let's wait for his reply....
Best regards,
Kaushik
Thank you for your kind words..
Actually it was my blind guess...
I just thought that we should first unprotect the sheet(s), then perform hide/unhide action and finally protect the sheet(s) again to avoid any potential error...
But no idea.....this is what all about 'HombreMan' is looking for
Let's wait for his reply....
Best regards,
Kaushik
Thank you!
I have only 1 "check box" button, and wrote hide/unhide macro using if statements. If False, do a hide rows and certain columns. If True, unhide etc etc.
Works great when the sheet is unprotected. If protected, errors and says it can run debug, etc.
I think what you are saying is to unprotect the sheet, do the hide/unhide, and protect it, all in the Macro.
Sounds like that might work! Thanks.
I am trying to make a complicated workbook into a chargeable product, and I am NOT a programmer--just a pretty good Excel user. If anyone can make it more of a product so it does not look like Excel, let me know--or what to do. Add-ins can help< i am told, but I think I am not capable of that--I can write complicated Macros, but that is all.
Someone want to help--let me know.
Thank you all.
I have only 1 "check box" button, and wrote hide/unhide macro using if statements. If False, do a hide rows and certain columns. If True, unhide etc etc.
Works great when the sheet is unprotected. If protected, errors and says it can run debug, etc.
I think what you are saying is to unprotect the sheet, do the hide/unhide, and protect it, all in the Macro.
Sounds like that might work! Thanks.
I am trying to make a complicated workbook into a chargeable product, and I am NOT a programmer--just a pretty good Excel user. If anyone can make it more of a product so it does not look like Excel, let me know--or what to do. Add-ins can help< i am told, but I think I am not capable of that--I can write complicated Macros, but that is all.
Someone want to help--let me know.
Thank you all.
SirJB7
Excel Rōnin
Hi, HombreMan!
If you haven't done it yet, add this two lines as first and last executable statments within your button's click event code section (aka ButtonName_Click() sub):
ActiveSheet.UnProtect
ActiveSheet.Protect
assuming that the sheet has no password, otherwise add this at the end of each statement:
"the_actual_password"
with a leading space and quoted.
Regards!
If you haven't done it yet, add this two lines as first and last executable statments within your button's click event code section (aka ButtonName_Click() sub):
ActiveSheet.UnProtect
ActiveSheet.Protect
assuming that the sheet has no password, otherwise add this at the end of each statement:
"the_actual_password"
with a leading space and quoted.
Regards!
How do you make it always full page, so all the ribbons are gone--no way for a client to un-full page it?
Or have it so it is always no address bar, always no gridlines or col/row labels? Then it might look more like a product. I have it protected, but it still looks like Excel......too much.
Or have it so it is always no address bar, always no gridlines or col/row labels? Then it might look more like a product. I have it protected, but it still looks like Excel......too much.
SirJB7
Excel Rōnin
Hi, HombreMan!
If you think you have your product protected, I'm afraid I have very bad news for you.
Begin changing your nick to MuertoMan, HombreDead or anything alike.
Go on reading this topics and the inside links:
http://chandoo.org/forums/topic/user-form-via-qat
http://chandoo.org/forums/topic/excel-password
Further steps should be leading you to convert your Excel workbook (spreadsheet or whatsoever the name you give to your Excel file) into .NET or other language or platform a bit more secure.
Regards!
If you think you have your product protected, I'm afraid I have very bad news for you.
Begin changing your nick to MuertoMan, HombreDead or anything alike.
Go on reading this topics and the inside links:
http://chandoo.org/forums/topic/user-form-via-qat
http://chandoo.org/forums/topic/excel-password
Further steps should be leading you to convert your Excel workbook (spreadsheet or whatsoever the name you give to your Excel file) into .NET or other language or platform a bit more secure.
Regards!