unable to set the visible property of the worksheet class

Aleciafxc

Board Regular
Joined
Mar 21, 2009
Messages
77
Various sheets are hidden and made visible through macros. Every once in a while sheets will be hidden and i cannot get them unhidden. When I right click on a sheet name the hide, unhide options are grayed out.

I have tried

Sub UnHideSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

ws.Visible = xlSheetVisible
Next ws
End Sub

This does not work. When I attempt to access the sheet in the Developer and set its property to xlsheetvisable this error comes up

unable to set the visible property of the worksheet class

any help would be appreciated.

thanks
Alecia :(
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
When you get the error, is the workbook's structure protected?
 
Upvote 0
Worksheets are protected, yes. Workbook structure No. When exiting all worksheets are set to visible false. This all works fine except occasionally. I have reviewed all of my macros that seem to set off this error but i can't see one that is wrong and not like the others that do not cause the error. It seems that I may have accidentally set something but not sure what i should look for.

I hope that makes sense. If I know what would do this then maybe i can search for that and find it.

Alecia
 
Upvote 0
When exiting all worksheets are set to visible false
hmmm.... at least 1 worksheet must be left visible at all times, so you would get an error if you try to hide all of them.

However, the code you presented is to show the sheets rather than hide them. Please can you confirm that it is trying to show the sheets that generates the error? If yes, how did you call the UnHideSheets() procedure and which code module does it reside in?
 
Last edited:
Upvote 0
I ran the code in an effort to unhide the sheets that won't unhide. I do always have one sheet visible and that is the StartUp sheet. I ran the above code from an object on that sheet with assigned macro to the object, I was not in design mode. I get the error when I go to the Developer and choose a worksheet that will not unhide, go to its visible property and try to set it to, -1 xlsheetvisible.

What would I have had to set so that afterwards i could not unhide a specific sheet(s).

Thanks
Alecia
 
Upvote 0
Sorry, I don't have any other suggestions. The error you get and the hide/unhide options for each sheet being greyed out both happen when the workbook structure is protected. I can't think of a different cause based on the information on the thread. I assume this is not an issue for all workbooks.

I know you've said that the workbook protection is not the problem, but just to be sure what happens if you run this code?
Code:
Sub UnHideSheets()
    Dim ws As Worksheet
    
    ActiveWorkbook.Unprotect "aaa"
    
    For Each ws In ActiveWorkbook.Worksheets
    
        ws.Visible = xlSheetVisible
    Next ws
    
End Sub
 
Upvote 0
I'm having the same problem but using an array of sheets to unhide/hide.

Is it National Anti-Sheet Hide/Unhide Macro Day?
 
Upvote 0
Ok, that helped. Someone accidentally password protected the workbook. Luckily they used a "normal" password. Sooooo, how can I keep that from happening. Any ideas.

Alecia
 
Upvote 0
One option would be to beat them to the punch and protect the workbook structure yourself. If you do that you will have to temporarily unprotect and then reprotect it in your code. Use errorhandling to try to ensure that the reprotection always occurs.
 
Upvote 0
That is a great suggestion. Error handling, something like: if workbook password is not "aaa" then do not set password??? Not quite sure how to write it. Any help would be appreciated.

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top