This is the second of 3 problems I currently have.
I have put a macro on the attached workbook sample and it works well and when the button is pressed it hides the columns and then reveals them again
But it only works when the workbooked is unprotected. Which leaves all the formulas that people on this forum have worked hard on, to get me this far. It says '
Runtime error 1004 Unable to set the hidden property of the range class'
Any ideas what this means and how I can resolve it please?
Thanks
You need to put a line of code in to unprotect the sheet before making changes
and then another line of code after to reprotect the sheet
you should always declare the sheet name if running code from a module unless it is in a sheet module.
this should do it
Regards
Sean
Please add to my reputation if you think i helped (click on the star below the post)
Mark threads as "Solved" if you have your answer (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [code]
Please supply a workbook containing example Data: It makes its easier to answer your problem & saves time!
I can see two problems. For Pwd to be used in multiple macros, it must be Dim'ed above the macros (to make it global)
The next problem is that the workbook open macro is in the wrong place. It should be in the workbook code area rather than a module.
Gary's Student
Thank you for that advice, unfortunately I don't know how or what code to put in the protect or unprotect and have even less idea about Dime'd.
I can start though by seeing if I can move the 'open' macro
I've now moved the 'open' macro to the workbook and deleted the module. I've looked at the protect un protect bit of code but I'm out of my depth on that one.
whats the password? i cannot fix it without it?
This is the latest version, now unprotected and with the offending module deleted.
Sean, sorry. I realised an uploaded an unprotected version, but the password is ic123
this should now work
Thanks again Sean for your time, the hide/unhide works now but unfortunately It asks for a password for both actions, I don't want to try to change anything in case I mess it up
if you dont want to ask for the pass word change this line
to
Please Login or Register to view this content.
in both sheet2 & sheet3 modulesPlease Login or Register to view this content.
that way it will know the password without asking for it
Thanks again, it seems a bit complicated, but it worked. I don't understand how the hide/unhide feature became pasword protected, it was never intended to be like that.
My intention was to just have an easy way to protect and unprotect the workbook as a whole.
The workbook will end up with about 35 to 40 sheets, does that mean I will have to change all of them to get it to hide/unhide? or if I just copy the sheets and then change their names will the codes move with them?
the reason being is your sheet is protected and therefore you can not change /hide columns whilst protected.
yes you can copy the sheet and it will copy all code in the sheets module, but you will need to update the code.
ie if you copy sheet 1 to sheet4, in the code you will need to change Sheet1.protect to Sheet4.protect.
you could also use some code to unprotect all worksheets but that will get a bit complicated.
let me know if you want some more help. i can possibly take another look tomorrow.
Thanks again Sean for taking the time to explain. I'm not quite ready to copy all the other worksheets yet, but when I do it shouldn't take too long to change the bits needed and it will be worth it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks