CODEWITHSUNDEEP
excelvba
Dan Hopkins
Dan Hopkins

Reputation: 9

List hidden worksheets

I have a materials register I am creating

Due to regulation when a material (each material has its own worksheet with a 3 digit random number added on the end to allow the same name multiple times) is deleted it cannot actually be deleted, so to work around this my workbook hides the sheet and using a deletion check on the summary page hides the appropriate row.

However what I am struggling with is a function to restore the sheet,

I have the code I need to do this however I cannot find any function to list hidden sheets.

This list can be put into the work book in a hidden column so I can reference it with my macro but as I said I cannot find anyway to list only sheets that are hidden.

Thanks for your help

Upvotes: 0

Views: 174

Answers (3)

brettdj
brettdj

Reputation: 55672

Hidden sheets can be Hidden or VeryHidden, to capture these:

ub ListEm()
Dim ws As Worksheet
Dim StrHid As String
Dim strVHid As String
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Visible
    Case xlSheetVisible
    Case xlSheetHidden
    StrHid = StrHid & ws.Name & vbNewLine
    Case Else
    strVHid = strVHid & ws.Name & vbNewLine
    End Select
Next
If Len(StrHid) > 0 Then MsgBox StrHid, vbOKCancel, "Hidden Sheets"
If Len(strVHid) > 0 Then MsgBox strVHid, vbOKCancel, "Very Hidden Sheets"
End Sub

Upvotes: 0

DMDesign
DMDesign

Reputation: 21

You could add to your code that does the hiding to write the name of the sheet that it is hiding to your other hidden tab, and add the reverse to your code that unhides it.

Not sure if the below is applicable to your situation, but you could also put some code in worksheet events to capture when the sheet is being made invisible

Private Sub Worksheet_Deactivate()
    If Me.Visible = xlSheetHidden Then MsgBox "I have been hidden"
End Sub

Upvotes: 1

user3849533
user3849533

Reputation: 1

Does this help ..

' Function to be used in array formula on sheet to list hidden sheets
Public Function ListHiddenSheets()
    Dim hiddenSheets As New dictionary

    Dim sheet As Worksheet
    For Each sheet In Worksheets
        If sheet.Visible <> xlSheetVisible Then hiddenSheets.Add sheet.Name, Null
    Next sheet

    Dim vRes() As Variant
    ReDim vRes(0 To hiddenSheets.Count, 0 To 0)

    Dim idx As Integer
    For idx = 0 To hiddenSheets.Count - 1
        vRes(idx, 0) = hiddenSheets.keys(idx)
    Next idx

    ListHiddenSheets = vRes
End Function

?

Upvotes: 0

Related Questions