Visual Basic Error – Worksheet Visibility

Topic

New Reply

I use Excel 2003 (I need to as an essential feature for me has been withdrawn for later versions) and Windows 10. Some time ago a VBA facility was kindly written for me that identifies the visibility status of a worksheet, ie Visible, Hidden, Very Hidden. I’ve copied it below. It’s been a great help for me. Unfortunately it does not work for macro sheets. sName argument as below example is [myBook.xls]mySheet. I’d really appreciate any help in fixing this problem.

Regards

Geoffrey

Function WorksheetStatus(sName As String)
Dim sWbkName As String
Dim sTemp As String
Dim X As Integer
Dim iStatus As Integer

On Error GoTo MyError

X = InStr(sName, “]”)
If X = 0 Then ‘No workbook
sWksName = Worksheets(sName).Name
iStatus = Worksheets(sWksName).Visible
Else ‘There is a workbook name
sWbkName = Mid(sName, 2, X – 2)
sWksName = Mid(sName, X + 1)
iStatus = Workbooks(sWbkName).Worksheets(sWksName).Visible
End If
Select Case iStatus
Case xlSheetVisible
WorksheetStatus = “Visible”
Case xlSheetHidden
WorksheetStatus = “Hidden”
Case xlSheetVeryHidden
WorksheetStatus = “VeryHidden”
Case Else
WorksheetStatus = “Error”
End Select
Exit Function
MyError:
WorksheetStatus = “Error”
Exit Function
End Function

Reply | Quote

Replies

Geoffrey,

Try your code with the modified lines in blue. Macro sheets are sheets not worksheets.

HTH,
Maud

Code:

Function WorksheetStatus(sName As String)
 Dim sWbkName As String
 Dim sTemp As String
 Dim X As Integer
 Dim iStatus As Integer

 On Error GoTo MyError

 X = InStr(sName, “]”)
 If X = 0 Then ‘No workbook
 [COLOR=”#0000FF”]sWksName = sName[/COLOR]
 [COLOR=”#0000FF”]iStatus = Sheets(sWksName).Visible[/COLOR]
 Else ‘There is a workbook name
 sWbkName = Mid(sName, 2, X – 2)
 sWksName = Mid(sName, X + 1)
 [COLOR=”#0000FF”]iStatus = Workbooks(sWbkName).Sheets(sWksName).Visible[/COLOR]
 End If
 Select Case iStatus
 Case xlSheetVisible
 WorksheetStatus = “Visible”
 Case xlSheetHidden
 WorksheetStatus = “Hidden”
 Case xlSheetVeryHidden
 WorksheetStatus = “VeryHidden”
 Case Else
 WorksheetStatus = “Error”
 End Select
 Exit Function
MyError:
WorksheetStatus = “Error”
 Exit Function
 End Function

Reply | Quote

Hi Maud

Nice fix!

Macro sheets are sheets not worksheets

But, curiously, if you selected a macro sheet, and then run this code:

Code:

Sub showSheetType()

MsgBox TypeName(ActiveSheet)

End Sub

..it will say that the macro sheet is a worksheet!! Go figure.

If you run that macro on a Chart sheet, it will say it’s a Chart sheet.
If you run that macro on a Dialog sheet, it will say it’s a DialogSheet
(see demo file attached)

zeddy

Reply | Quote

Excellent, Thank you.

Reply | Quote

Hi Geoffrey

I need to as an essential feature for me has been withdrawn for later versions

Perhaps there may be a workaround for the ‘essential feature’ you require?
If you are able to tell us what it is, we might be able to suggest another way.

Although there are some things I miss from Excel2003, the newer capabilities of Excel2010 (and beyond) are overwhelmingly useful.

zeddy

Reply | Quote

Hi zeddy,

That is strange! Just as an experiment, place the following codes in a standard module then run each. Using the worksheets object return only standard worksheets. Using the Sheets object returns the standard worksheets as well as Macro and dialog sheets.

Code:

Sub TraverseSheets()
Dim ws As Sheets
For Each Sheet In Sheets
   MsgBox (Sheet.Name)
Next
End Sub


Sub TraverseWorkSheets()
Dim ws As Worksheets
For Each Sheet In Worksheets
   MsgBox (Sheet.Name)
Next
End Sub

Maud

Reply | Quote

Hi Maud

Yes, that is true.
And just to finish off, if you use
MsgBox ActiveSheet.Type

..you will see that a proper worksheet is Type= -4167.
..and Chart and Macro sheets are both the same Type= 3
..but DialogSheets don’t have any Type at all!

zeddy

Reply | Quote

As Arte Johnson would say, … https://www.youtube.com/watch?v=krD4hdGvGHM :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote