In MS Excel, hide multiple sheets using a single click is available i.e. Select multiple sheets by pressing control button, and then right click and select Hide Sheets. But this is not available for unhidden sheets.
Below is VBA add-in to unhide sheets using single click
Option Explicit
Sub UnHideSheets()
Dim i, cnt as integer
cnt = ActiveWorkbook.Worksheets.Count
i = 1
Do
Sheets(i).Visible = True
i = i + 1
Loop Until i > cnt
End Sub
===============================================================
8. New icon will be created at top
9. When you want to unhide sheets of any workbook click on the newly created button; it will show all hidden sheets from that workbook.
Below is VBA add-in to unhide sheets using single click
1. Open a New Excel File
3. Open Visual Basic Editor (VBE) using Alt + F11
4. Insert Module
5. Add below code and Close VBE
===============================================================
' Add-In Unhide Sheets using a Single Click
Sub UnHideSheets()
cnt = ActiveWorkbook.Worksheets.Count
i = 1
Do
Sheets(i).Visible = True
i = i + 1
Loop Until i > cnt
End Sub
===============================================================
6. Click on "Save As" file
7. Select "Excel Add-In" in the "Save-As Type" Dropdown and Save file as "Showsheets"
7. Save File as "Add-In" File
8. Close Excel File
Now, It should be available for display sheets using single click
Manage Add-In
1. Open MS Excel
2. Click on File -> Options For Excel 2010 (For Excel 2007 - Click On "Office" Button and then click on "Excel Options")
3. Select Add-Ins and then from "Manage" Drop Down (At Bottom) - Select "Excel Add-In" And then Click on "Go"
4. Select "ShowSheets" and Click on OK
Map Add-In to Button
1. Click on File -> Options (For Excel 2007 - Click On "Office" Button and then click on "Excel Options")
2. Select "Quick Access Toolbar" For Excel 2010 (For Excel 2007 - Click On "Customize")
3. Select “Macros” from “Choose Commands from”
4. Select "UnHideSheets" and Add
5. Then Under “Customize Quick Access Toolbar”
a. Select "For all Documents (Default)"
b. From Below List Select "UnHideSheets" and Click on Modify
6. Select any sign & give any display name that you like to view then Click Ok
7. Click “Ok” , Save & Close file
9. When you want to unhide sheets of any workbook click on the newly created button; it will show all hidden sheets from that workbook.
No comments:
Post a Comment