Click a button to make specific worksheets hidden or visible (vba)
Click button "Show / Hide worksheets" to show or hide worksheets entered in cell range B6:B7.
Instructions
Here is how I did it.
Create a button
- Go to Developer tab on the ribbon
- Click "Insert" button
- Click "Button" button
- Drag on sheet to make a button
- Click on button to rename
VBA Code
Sub ShowHideWorksheets()
Dim Cell As Range
For Each Cell In Range("B6:B7")
ActiveWorkbook.Worksheets(Cell.Value).Visible = Not ActiveWorkbook.Worksheets(Cell.Value).Visible
Next Cell
End Sub
- Go to VB Editor (Alt + F11)
- Click Insert on the menu
- Click Module
- Paste code to module
- Exit VB Editor and return to excel
Assign macro to button
- Right click on button
- Click "Assign macro..."
- Select macro ShowHideWorksheets
- Click OK
Download excel *.xlsm file
Click a button to make specific worksheets hidden or visible (vba).xlsm
This post describes how to copy values between sheets. I am using the invoice template sheet. This macro copies rows […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specified folder and create adjacent checkboxes, using vba. The […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
Change chart data range using a Drop Down List [VBA]
This blog post demonstrates how to quickly change chart data range. I have created a drop down list (form control) […]
List all hyperlinks in worksheet
The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" […]
4 Responses to “Click a button to make specific worksheets hidden or visible (vba)”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Use the img tag, like this: <img src="Insert pic link here">
Welcome! I am 
){kind=link}
how to create a command button for show/hide columns in protect sheet?
I used the above instructions with 99% success. My sheets all have names so I copied and pasted the names of those sheets into the cell range of D7:D13. Therefore I used that range in the module in place of "B6:B7", however, for some reason, one of the sheets doesn't ever hide or unhide. Possible solutions?
how can i show and hide 3 charts on activesheet using one command button hide/unhide chart1, chart2, chart3
Sim
Sub ShowHideCharts() With ActiveSheet.ChartObjects("Chart 1") .Visible = Not .Visible End With With ActiveSheet.ChartObjects("Chart 2") .Visible = Not .Visible End With With ActiveSheet.ChartObjects("Chart 3") .Visible = Not .Visible End With End Sub