Hiding/Displaying Sheets with VBA - Strange Behavior

proszak59

New Member
Joined
Sep 20, 2014
Messages
4
I am running Excel 2013 with Windows 7. I have a table built with a list of all the sheet names in the workbook. The table contains the Display State of the sheet (Hidden, Visible, Very Hidden). In the workbook open sub procedure I have the following code.

' hide/show sheets based on development mode or production mode
If cDevelopment_Mode = True Then
For i = 1 To Sheets.Count
Sheets(i).Visible = xlVisible
Next
Else
On Error Resume Next
Sheet_Name_Col = Find_Field_Col(tblSheet_Display, "Sheet_Name")
Display_State_Col = Find_Field_Col(tblSheet_Display, "Display_State")
With Range(tblSheet_Display)
For i = 2 To .Rows.Count + 1
Select Case LCase(.Cells(i, Display_State_Col))
Case "visible"
temp = .Cells(i, Sheet_Name_Col)
Sheets(temp).Visible = xlSheetVisible
Case "hidden"
Sheets(.Cells(i, Sheet_Name_Col)).Visible = xlSheetHidden
Case "very hidden"
Sheets(.Cells(i, Sheet_Name_Col)).Visible = xlSheetVeryHidden
End Select
Next
End With
On Error GoTo 0
End If

When I try to resolve the Sheet name in the Sheets statement it resolves correctly when I check in the debugger bug I get Type Mismatch Error. If I resolve the name and store it in a string variable then use it in the sheets statement it works. In the example above the visible case works but the hidden/very hidden case doesn't. I guess I could go through the extra steps of assigning it to a variable first but don't understand why I would have to do that.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
What line errors?

Why do you use On Error Resume Next?
 
Upvote 0
the following line gets the error:

Sheets(.Cells(i, Sheet_Name_Col)).Visible = xlSheetHidden

I use the On Error Resume Next because the application creates a number of new sheets. When the file opens the sheets may not be present so if it tries to set the visible property of a sheet that doesn't exist is just continues to the next one.
 
Upvote 0
What's the sheet name that fails?

How can you get a run-time error with OERN?
 
Upvote 0
the 1st sheet in the table fails Enrollment. In the Visible Case Statement I assigned the Sheet name from the table to a String Variable and then use that in the Sheets.Visible statement and it works just fine. The problem only occurs when I try to use the sheet name directly from the table in the Sheets.Visible statement. if you look at the code the way I do it in the Visible Case works but it fails when I do it the way I want in the Hidden and Very Hidden Case.

I don't now what OERN is?
 
Upvote 0
Got it! Actually since the sub procedure wasn't producing the results when I had the OERN statement I commented it out to debug it so you are correct when OERN is on it does not error out but does not work.
 
Upvote 0
So you don't get a type mismatch error, or any other error? Color me confused.
 
Upvote 0
So you don't get a type mismatch error, or any other error? Color me confused.
I think what the OP is saying is his code was not producing the result he expected, so he commented out the OERN statement to debug the code and it is then that executing the indicated code line produced the error.



the following line gets the error:

Sheets(.Cells(i, Sheet_Name_Col)).Visible = xlSheetHidden

I use the On Error Resume Next because the application creates a number of new sheets. When the file opens the sheets may not be present so if it tries to set the visible property of a sheet that doesn't exist is just continues to the next one.
It seems that Excel is not able to reference the object of the With statement in the Immediate Window (trust me, I find this quite annoying as well), so your .Cells call is generating the error. If you copy/paste Range(tblSheet_Display) in front of the dot in .Cells for that code, it should work.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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