If myValue = "***" Then 'User wants all worksheets displayed If Sheets(Sheetnum).Visible = 0 Then Sheets(Sheetnum).Visible = True If myValue = "**" Then 'User wants all the Normal Hidden sheets displayed If Sheets(Sheetnum).Visible = 2 Then Sheets(Sheetnum).Visible = True If myValue = "*" Then 'User wants all the VeryHidden sheets displayed If myValue = "" Then Exit Sub 'User pressed CANCEL MyValue = InputBox(Message, Title, Default) 'Display the Message Box and retrive the user's input Message = Message & "Or * - All Invisible, ** - All Hidden, *** - All" & vbCrLf & vbCrLf Message = "Enter the 'Name' of the WorkSheet to Unhide" & vbCrLf MyList = myList & vbCrLf & " No Hidden Sheets in This Workbook" MyList = myList & vbCrLf & vbCrLf & "'HIDDEN WORKSHEET NAMES(**)':" MyList = myList & vbCrLf & " No Invisible Sheets in This Workbook" If myList = "'INVISIBLE WORKSHEET NAMES(*)':" Then MyList = myList & vbCrLf & " " & Sheets(Sheetnum).Name MyList = "'INVISIBLE WORKSHEET NAMES(*)':"
' *** will unhide all worksheets in the workbook ' name of the worksheet they want to unhide. ' VeryHidden worksheets in the workbook and allows the user to enter the ' Provides an input dialog box that displays the names of all Hidden and all
I use Dave's Mappit Addin noted above for the more detailed analysis. This does not show the hidden cells/rows/columns but has been very useful for finding and unhiding sheets. It displays all hidden sheets and very hidden sheets in a pop up dialog that also gives you the ability to unhide a single sheet or all Hidden, all veryHidden or both. I keep the procedure below in my Personal.xls file and have a button on the Quick Access Toolbar to run it. Please note that you can also use the "inspect document" feature in Excel to see if a document has hidden sheets/rows/columns. RCount & " hidden rows found." & vbLf & _ MsgBox sCount & " hidden sheets found." & vbLf & _ If rng.Hidden = True Then cCount = cCount + 1 If rng.Hidden = True Then rCount = rCount + 1įor Each rng In wks.Columns ' or If wks.Visible = xlSheetVeryHidden Then sCount = sCount + 1įor Each rng In wks.Rows ' or If wks.Visible = xlSheetHidden Then sCount = sCount + 1
Note that using the 'UsedRange' for rows/columns means that the count will not include rows/columns that do not contain any data (but the macro will be faster as a result).ĭim sCount As Long, rCount As Long, cCount As Long.Here is a method, very similar to Banjoe's, that will return the count of how many hidden sheets, columns, and rows there are (assuming you don't need the info on which rows and just want a report).
0 kommentar(er)
