Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Visual Basic Error - Worksheet Visibility

    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

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    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
     sWksName = sName
     iStatus = Sheets(sWksName).Visible
     Else 'There is a workbook name
     sWbkName = Mid(sName, 2, X - 2)
     sWksName = Mid(sName, X + 1)
     iStatus = Workbooks(sWbkName).Sheets(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

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    Antediluvian (2016-04-24)

  4. #3
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Excellent, Thank you.

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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
    Attached Files Attached Files

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    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

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    RetiredGeek (2016-04-24)

  9. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    As Arte Johnson would say, ...
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •