[Fixed!] Unable to Set the Visible Property of the Worksheet Class

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for some special tricks to solve the problem of unable to set the visible property of the worksheet class, you’ve come to the right place. In Microsoft Excel, there are numerous ways to fix this problem. This article will discuss three methods to resolve the problem. Let’s follow the complete guide to learn all of this.


Unable to Set the Visible Property of the Worksheet Class Error: 3 Possible Solutions

In the following section, we will use three effective and tricky solutions to solve the problem of “unable to set the visible property of the worksheet class”. Firstly, we will try to solve the problem by unprotecting the worksheet from the review tab in MS Excel. We will use the VBA code to solve the problem in the second and third solutions. This section provides extensive details on these solutions. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference. There are times when trying to change the worksheet’s visible property in Microsoft Excel results in an error message reading “unable to set the visible property of the worksheet class”. The problem will look like this.

Unable to Set the Visible Property of the Worksheet Class

Now, we are going to demonstrate how we can solve the problem.


Solution 1: Unprotect Your Worksheet from Review Tab

The visible property of a worksheet class can sometimes be unset in Microsoft Excel due to an error message reading “unable to set the visible property of the worksheet class.” You will get an error message if you try to change the visible property of Sheet3 after opening the VBA window, as shown below.

Unprotect Your Worksheet to solve "Unable to Set the Visible Property of the Worksheet Class"

The fact that the workbook or worksheets are protected is the primary cause of this happening. Visibility can be set only after the workbook and worksheets have been unprotected. To solve this problem, first of all, you have to open Sheet3 and go to the Review tab and select Unprotect Sheet.

Unprotect Your Worksheet

Next, when the Unprotect Sheet window appears, type the password and click on OK.

Now, if you try to change the visible property of Sheet3 after opening the VBA window, you will not receive an error message.

select visible option

Read More: VBA Object Variable or with Block Variable Not Set in Excel


Solution 2: Close Other Workbooks While Running Macro

Now, if you are running macros while opening multiple workbooks, VBA won’t find the sheet references. For that reason, you have to mention the workbook name. Or, you can run the particular macro keeping other workbooks closed. If you want to solve the problem of “unable to set the visible property of the worksheet class”, you need to use the help of VBA. Microsoft Visual Basic for Applications (VBA) is Microsoft’s Event Driven Programming Language. To use this feature you first need to have the Developer tab showing on your ribbon. Click here to see how you can show the Developer tab on your ribbon. Once you have that, follow these detailed steps to solve the problem of “unable to set the visible property of the worksheet class”,

📌 Steps:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developers tab on your ribbon. Then select Visual Basic from the Code group.

close other workbooks during running macro to solve "Unable to Set the Visible Property of the Worksheet Class"

  • VBA modules hold the code in the Visual Basic Editor. It has a .bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

insert module

  • As a result, a new module will be created.
  • Now select, the module if it isn’t already selected. Then write down the following code in it. Make sure other workbooks are closed before running the following macro.
Sub solved()
    Dim Msheet As Excel.Worksheet
    For Each Msheet In Worksheets
        Msheet.Visible = xlSheetVeryHidden
    Next Msheet
End Sub
  • Next, save the code.
  • Lastly, you have to click on Run to run the macro.

Now, if you try to change the visible property of any sheet after opening the VBA window, you will not receive an error message.  This is how you will be able to solve the problem.

Read More: [Fixed!] Subscript Out of Range Error in Excel VBA


Solution 3: Unprotect and Re-Protect Your Worksheet

Now, we will show another VBA code to solve the problem. If you want to solve the problem of “unable to set the visible property of the worksheet class”, you need to use the help of following the VBA code. You have to follow these detailed steps to solve the problem of “unable to set the visible property of the worksheet class”,

📌 Steps:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developers tab on your ribbon. Then select Visual Basic from the Code group.

Unprotect and re-protect Worksheet to solve "Unable to Set the Visible Property of the Worksheet Class"

  • VBA modules hold the code in the Visual Basic Editor. It has a .bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.

  • As a result, a new module will be created.
  • Now select, the module if it isn’t already selected. Then, write down the following code in it.
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
  • Next, save the code.
  • Lastly, you have to click on Run to run the macro.

run macro

Now, if you try to change the visible property of any sheet after opening the VBA window, you will not receive an error message.  This is how you will be able to solve the problem.

Read More: Reasons And Solutions for Excel Object Required Error in VBA


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets and methods in different spreadsheets for a clear understanding.


Conclusion

That’s the end of today’s session. I strongly believe that from now, you may solve the problem of “unable to set the visible property of the worksheet class”. If you have any queries or recommendations, please share them in the comments section below.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

6 Comments
  1. 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.

    • 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

  2. 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.

    • 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

  3. 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.

    • 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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo