I recently posted how to Unhide All Worksheets and John Mansfield asked how do you approach the problem if the VBA Project is protected?
A good question since the solution could require running new code in a VBA Project that’s protected. I didn’t have an immediate answer, however Ross had a good idea.
In summary:
1. In a blank workbook enter your code in the Visual Basic Editor
2. Save the blank workbook as an addin (.xla file)
3. In the workbook that is protected, import the addin you just created and run the addin code
Update 12-28-04: Nick Burns points out (see comments) that the immediate window in the VBA project serves the same purpose
December 27th, 2004 at 11:58 pm
Instead of using an addin, in the immediate window you can type the code from the previous entry as so:
for each ws in Worksheets:ws.Visible=true: next ws
I’ve tested this and it successfully unhid all hidden worksheets.
December 28th, 2004 at 12:09 am
Nick you are correct. Funny thing is John Peltier mentioned that in the first comment to the unhide worksheet post.
Guess we wanted to take the scenic route
I expect the post will still be beneficial if a large portion of code needed to be run.
Thanks for pointing it out.
mark
December 28th, 2004 at 11:48 am
Hi,
This got me thinking, I should be doing some work tonight, but i’m not, i’m gonna play around with something! oh excitment!
December 28th, 2004 at 1:15 pm
no what i was trying wont work, cool!
From Chip Pearsons Site:
NOTE: In all versions of Excel, the VBProject must not be protected. If it is, these procedures will fail.
can lay that one to bed now!, didn’t think it would - would of been a bit of a cock up even for M$
December 28th, 2004 at 1:25 pm
I’m assuming you were trying to manipulate the code in the protected workbook from the addin or the immediate window? Possibly delelete all the code or copy all code?
If so, that’s amusing because I had the same thought, I just didn’t get around to trying it.
December 29th, 2004 at 8:22 am
Yeah that’s right Mark!, I did think that it would work, but you just don’t know! lol!