Do macros work on hidden sheets?
|
on August 8, 2011, 12:08 AM PDT Unhiding Excel sheets is easy, but can be tedious. Use this simple macro to unhide all hidden sheets in an Excel workbook. We hide sheets for many reasons, but mostly, to keep other people out of them. We rarely hide them from ourselves. When you need to update or fix a workbook for a user, you have to remember the hidden sheets and then unhide them – which is easy enough, unless you removed that
functionality from the workbook! To unhide sheets, click any sheet tab and choose Unhide from the context menu. Then, select the sheet you want to unhide from the list and click OK. Although easy, unhiding sheets in this manner is tedious if there happens to be several of them. Doing this several times to unhide all hidden sheets isn’t necessary. Here’s a quick macro that you can copy into almost any workbook to quickly unhide sheets: Sub UnhideAllSheets()
‘Unhide all sheets in workbook. Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub In a nutshell, a For Each loop cycles through all the sheets in the Worksheets collection and sets each sheet’s Visible property to true. This macro will even unhide sheets you hide via the Visual Basic Editor properties (xlSheetVeryHidden) so be careful how you apply it. To run the macro, click Macros in the Code group on the Developer tab. Or, add it to the QAT or a custom tab. Like most macros, this one has limited appeal. If you have only a few hidden sheets and you seldom need to unhide them, it’s just as easy to manually unhide them. If, on the other hand, this is a frequent task, you’ll probably find this one useful. It’s a good demonstration of how easy it is to cycle through an object collection. You could add an If() statement that checks for the Visible property and then change only the ones that require it, but this loop is more efficient. Just reset them all; in this case, an If() just adds more work. However, if you want to avoid unhiding certain sheets or the “very hidden” sheets, an If() statement will do the trick.
You can use a VBA code to hide or unhide a sheet in Excel. When you right-click on the sheet tab, you can see the option to hide or unhide it, and that same thing you can do with a VBA code. In this post, we will look at some of the ways and methods that we can use. Let’s say you want to hide “Sheet1” from the active workbook. In that case, you need to use code like the following. In the above code, you have referred to Sheet1, use the visible property, and changed it to false. Make a Sheet Very HiddenThere’s one more option that you can use to make a sheet very hidden that cannot be un-hide by the user easily.
Hide a Sheet Based on the Value from a CellAlright, if you want to use a cell value instead of directly using the sheet name in the code, you can refer to that cell. This code refers to cell A1 and uses the value from it to refer to the sheet that you want to hide. Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook Check Sheet Before HidingYou can also use a small code like the following to check the sheet that you want to hide exits or not. The above code uses the FOR EACH LOOP + IF STATEMENT to loop through each sheet in the workbook. And check for the sheet that you want to hide. Hide All the Sheets (Except ActiveSheet)Now there one thing that you need to understand you can’t hide all the sheets. There must be one sheet visible all the time. The above code loops through all the sheets of the workbook, then match each sheet’s name with the active sheet’s name and hide it if it doesn’t match. VBA Code to Unhide a SheetTo unhide a sheet, you need to change the visible property to TRUE. If the sheet that you want to unhide it already visible, this code won’t show any error. But if that sheet doesn’t exist, then you’ll get a Run-time error ‘9’. Use VBA to Unhide All the Hidden SheetsImagine you have more than one hidden sheet in a workbook, and if you want to hide them manually, you need to do this one by one. But here’s the code does this in one go. It loops through each sheet and un-hides it. More Tutorials on VBA Worksheets
Can you hide tabs in Excel but still use them?You can hide any worksheet to remove it from view. The data in hidden worksheets is not visible, but it can still be referenced from other worksheets and workbooks, and you can easily unhide hidden worksheets as needed. Right-click the sheet tab you want to hide, or any visible sheet if you want to unhide sheets.
Can you run a macro on a different sheet?If a workbook contains a Visual Basic for Applications (VBA) macro that you would like to use elsewhere, you can copy the module that contains that macro to another open workbook by using the Visual Basic Editor (VBE).
|
