Unable to Set the Visible Property of the Worksheet Class

Unable to Set the Visible Property of the Worksheet Class – 3 Solutions

Last updated: May 1, 2024

Sometimes trying to change the visible property in Microsoft Excel results in the error message “unable to set the visible property of the worksheet class”.

Solution 1 – Unprotect Your Worksheet in the Review Tab

When the visible property of a worksheet class is unset , you will get an error message if you try to change it after opening the VBA window.

This usually happens when the workbook or worksheets are protected. Visibility can be set only after unprotecting the sheet or book:

Open Sheet3, go to the Review tab and select Unprotect Sheet.

In the Unprotect Sheet window, enter the password and click OK.

The error message will no longer be displayed.

Solution 2 – Close Other Workbooks While Running Macros

If you are running macros while opening multiple workbooks, VBA won’t find the sheet references. You have to mention the workbook name or run the macro keeping other workbooks closed.

Steps:

To open the VBA window, go to the Developer tab on the ribbon.
In Code, select Visual Basic.

To insert a module for the code, go to the Insert tab.
Click on Module.

A new module will be created.
Select the module and enter the following code (other workbooks must be closed before running the macro).

Sub solved()
    Dim Msheet As Excel.Worksheet
    For Each Msheet In Worksheets
        Msheet.Visible = xlSheetVeryHidden
    Next Msheet
End Sub

Save the code.
Click Run to run the macro.

No error message will be displayed.

Solution 3 – Unprotect and Re-Protect Your Worksheet

Steps:

Go to the Developer tab on the ribbon.
In Code, select Visual Basic.

Go to the Insert tab.
Click Module.

A new module will be created.
Select the module and enter the following code.

Private Sub Workbooks_Opening()
    Dim wss As Worksheet
    ActiveWorkbook.Unprotect "1055"
    ActiveWorkbook.Worksheets("Split1").Visible = True
    ActiveWorkbook.Worksheets("Split2").Visible = False
    For Each wss In ActiveWorkbook.Worksheets
        If Not wss.Name = "Split1" Then wss.Visible = xlSheetVeryHidden
    Next wss
    With ActiveWorkbook.Worksheets("Split1")
        .Visible = True
        .Activate
    End With
    frmLogin.Show
    bBkIsClose = False
    ActiveWorkbook.Protect "1055", True, False
End Sub

Save the code.
Click Run to run the macro.

No error message will be displayed.

Download Practice Workbook

Download this practice workbook to exercise.

Unable to Set the Visible Property.xlsm

Get FREE Advanced Excel Exercises with Solutions!

6 Comments

aditya Feb 1, 2023 at 10:37 AM

Thank you Brother. In my case, i found that i just have to unprotect the workbook. Since im using dinamic VBA code that need to make set the chosen sheet to be visible while the rests are very hidden.
- Reply
  Yousuf Khan Mar 9, 2023 at 5:52 PM
  
  Hello ADITYA,
  
  You’re welcome! We are glad to hear that you found the solution to your issue. Unprotecting the workbook can indeed resolve the issue of being unable to set the visible property of the Worksheet class. It’s great to see that your dynamic VBA code is working as expected. Let us know if there’s anything else we can help with.
  
  Regards,
  Yousuf Khan
Michael Mar 6, 2023 at 9:49 AM

Hello Yousuf,

I have a protected worksheet and it is hidden.
I have cracked the password to unprotect the sheet.
The worksheet is still hidden and unable to hide or unhide as both options are grayed out. In VBA, i tried to code it to all sheets to xlSheetVisible or .Visible = True it pops up the same error as “Unable to set the Visible property of the Worksheet class”.
The worksheet currently has 0-xlSheetHidden.

Are you able to assist with this issue ?

Thank you in advance.
- Reply
  Yousuf Khan Mar 9, 2023 at 5:50 PM
  Hi MICHAEL,
  
  The errors you’ve mentioned could indicate that the workbook is either very hidden or corrupted. There are two levels of worksheet hiding: hidden and very hidden. From a user’s perspective, the difference is that a very hidden sheet cannot be made visible through the Excel user interface, and the only way to unhide it is with VBA.
  
  Since you’ve used VBA to work with the worksheets and they still appear hidden, you can try a different VBA code to check if they’re very hidden and unhide them. Here’s the VBA code to unhide all very hidden sheets:
```
Sub UnhideVeryHiddenSheets()
  Dim wks As Worksheet
  For Each wks In Worksheets
      If wks.Visible = xlSheetVeryHidden Then wks.Visible = xlSheetVisible
  Next
End Sub
```
  Note: This code only works for very hidden sheets, not worksheets that are hidden normally. If you want to display all hidden sheets, use the code below.
```
Sub UnhideAllSheets()
  Dim wks As Worksheet
  For Each wks In ActiveWorkbook.Worksheets
    wks.Visible = xlSheetVisible
  Next wks
End Sub
```
  Give it a try and let me know if it works.
  
  Best Regards,
  Yousuf Khan
Pepijn Koops Jan 9, 2024 at 9:01 PM

not one of the three proposed solutiond did work for me, the same error message keeps on returning, even when I switch off all protections on workbook and on worksheet level.
- Reply
  Aniruddah Alam Jan 17, 2024 at 3:27 PM
  
  Dear Pepijn,
  
  Thank you for sharing your problem. You may be experiencing the same issue as MICHAEL who shared his problem in the comment section above. Please try the solution suggested by Yousuf and let us know if the problem still persists. You can also share your file in our forum for us to investigate the issue closely.
  Best of luck.
  
  Regards,
  Aniruddah

Unable to Set the Visible Property of the Worksheet Class – 3 Solutions

Solution 1 – Unprotect Your Worksheet in the Review Tab

Solution 2 – Close Other Workbooks While Running Macros

Solution 3 – Unprotect and Re-Protect Your Worksheet

Related Articles

Leave a reply Cancel reply