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