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 :(
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
When you get the error, is the workbook's structure protected?
 

Aleciafxc

Board Regular
Joined
Mar 21, 2009
Messages
77
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
 

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

Aleciafxc

Board Regular
Joined
Mar 21, 2009
Messages
77

ADVERTISEMENT

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
 

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

rdw72777

Well-known Member
Joined
Apr 5, 2005
Messages
723

ADVERTISEMENT

I'm having the same problem but using an array of sheets to unhide/hide.

Is it National Anti-Sheet Hide/Unhide Macro Day?
 

Aleciafxc

Board Regular
Joined
Mar 21, 2009
Messages
77
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
 

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Aleciafxc

Board Regular
Joined
Mar 21, 2009
Messages
77
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,054
Messages
5,628,335
Members
416,311
Latest member
S991102

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
Top