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

    Worksheet Hidden / Unhidden Status

    I wish to write a VBA module for Excel 2003 to return whether a sheet is HIDDEN or UNHIDDEN or in ERROR (if the sheet does not exist). By Hidden I mean as in the Format / Sheet / Hide menu commands. My VBA skills are very limited, and I wish to interface this to the old macro language. I envisage a call would look something like SheetHideStatus("[book1]Sheet2").

    I'd very much appreciate any assistance.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I never worked with the old macro language. here is a VBA function that does it:

    Code:
    Function WorksheetStatus(sWksName As String)
      Dim sName As String
      On Error Resume Next
      sName = Worksheets(sWksName).Name
      On Error GoTo 0
      If sName <> sWksName Then
        WorksheetStatus = "Error"
        Exit Function
      End If
      Select Case Worksheets(sWksName).Visible
        Case xlSheetVisible
          WorksheetStatus = "Visible"
        Case xlSheetHidden
          WorksheetStatus = "Hidden"
        Case xlSheetVeryHidden
          WorksheetStatus = "Very Hidden"
        Case Else
          WorksheetStatus = "Error"
      End Select
    End Function
    you can use a line like:

    MsgBox WorksheetStatus("Sheet1")

    or a setup with it going to a variable like this:
    dim sStatus as string
    sStatus = WorksheetStatus("Sheet1")

    of even testing it in an IF

    If WorksheetStatus("Sheet1") = "Hidden" then

    in your VBA code.

    Steve

  3. #3
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Steve,

    Thank you, I very much appreciate your advice, especially the Very Hidden part. It works well for Sheet1 but it's case sensitive and as far as I can tell does not handle [book]sheet requirements. I'm a bit worried it won't handle checking the status of sheets in workbooks other than the one from which I'm working. I've checked that if the macro language code selects the other workbook it then seems to work, but it would be more elegant to solve it in VBA as the macro language has so many limitations.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Try this. I used UCASE to make it case insensitive and it checks for the presence of a workbook name (in brackets) and parses it. If there is no workbook name given, it is assumed to be in the active workbook.

    Steve

    Code:
    Function WorksheetStatus(sName As String)  Dim sWksName As String
      Dim sWbkName As String
      Dim sTemp As String
      Dim x As Integer
      Dim iStatus As Integer
      
      x = InStr(sName, "]")
      If x = 0 Then 'No workbook
        On Error Resume Next
        sWksName = Worksheets(sName).Name
        On Error GoTo 0
        If UCase(sName) <> UCase(sWksName) Then
          WorksheetStatus = "Error"
          Exit Function
        End If
        iStatus = Worksheets(sWksName).Visible
      Else 'There is a workbook name
        sWbkName = Mid(sName, 2, x - 2)
        sWksName = Mid(sName, x + 1)
        On Error Resume Next
        sTemp = Workbooks(sWbkName).Name
        On Error GoTo 0
        If UCase(sTemp) <> UCase(sWbkName) Then
          'workbook does not exist
          WorksheetStatus = "Error"
          Exit Function
        End If
        On Error Resume Next
        sTemp = Workbooks(sWbkName).Worksheets(sWksName).Name
        On Error GoTo 0
        If UCase(sTemp) <> UCase(sWksName) Then
          'worksheet does not exist
          WorksheetStatus = "Error"
          Exit Function
        End If
        iStatus = Workbooks(sWbkName).Worksheets(sWksName).Visible
      End If
      Select Case iStatus
        Case xlSheetVisible
          WorksheetStatus = "Visible"
        Case xlSheetHidden
          WorksheetStatus = "Hidden"
        Case xlSheetVeryHidden
          WorksheetStatus = "Very Hidden"
        Case Else
          WorksheetStatus = "Error"
        End Select
    End Function

  5. #5
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Steve,

    Absolutely perfect. Many thanks.

    Geoffrey

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
  •