Saquib Ahmad Shuvo
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.
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.
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.
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.
Read More: [Fixed!] Excel Sheet Not Visible When Opened
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.
- 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. 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.
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.
- 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.
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.
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.
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
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:
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.
Give it a try and let me know if it works.
Best Regards,
Yousuf Khan