How to hide/unhide a set of worksheets using a Command Button in VB

How to hide/unhide a set of worksheets using a Command Button in VB or using a Macro, it will be great if someone can share the macro

Top Contributor Asked on January 26, 2017 in VBA: Macros.
Add Comment
2 Answer(s)

HI,
Here is the solution for your query to hide/unhide a set of worksheets using a command button.
Here are the instructions.
1. Go to VBA editor by clicking on Alt+F11.
2. Insert UserForm from the Insert Menu.
3. Drag and drop listbox control & two command button controls from the toolbox.
4. Right click on the Listbox and select properties. the ‘MultiSelect’ property should be ‘1-frmMultiSelectMulti’.
5. Select first command button.
6. Set first Command button caption property to ‘Hide.
7. Select second command button.
8. Set second Command button caption property to ‘UnHide.
9. Design of the userform for your reference in the below screenshot.

Hide Unhide Worksheets

10. Go to Sheet1.
11. Insert shape from the ‘Illustration’ group.
12. Right click on shape and edit text as Show Form.

Hide Unhide Worksheets

13. Right click on the shape(Step 11) and select’Assign Macro’ from the available options.
14. Click on New from the Assign Macro dialog box and then add following statement.

Sub Rectangle1_Click()
UserForm1.Show
End Sub

16. Now, go to VBA editor window. Double click on the userform and then add following code.

VBA Code to Hide Sheets:

Private Sub CommandButton1_Click()
'Variable Declaration
Dim iCnt As Integer
Dim count As Integer, lastRow As Integer, destCol As Integer
count = 0: destCol = 1
For iCnt = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCnt) = True Then
ShtName = ListBox1.List(iCnt)
Sheets(ShtName).Visible = False
End If
Next iCnt
'Unload userform
Unload Me
End Sub

VBA Code to UnHide Sheets

Private Sub CommandButton2_Click()
'Variable Declaration
Dim iCnt As Integer
Dim count As Integer, lastRow As Integer, destCol As Integer
count = 0: destCol = 1
For iCnt = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCnt) = True Then
ShtName = ListBox1.List(iCnt)
Sheets(ShtName).Visible = True
End If
Next iCnt
'Unload userform
Unload Me
End Sub

Load all available worksheet names to Listbox

Private Sub UserForm_Initialize()
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets
ListBox1.AddItem Sht.Name
Next
End Sub

17. Go to sheet and then click on ‘Show Form’

 18. Now you can select available Worksheet names and then click on Hide button to hide Worksheets.

 19. Now you can select available Worksheet names and then click on UnHide button to unhide Worksheets.

 Hope it clarifies your query.

Regards!

 

Expert Answered on January 27, 2017.
Add Comment

Try this File

 

Contributor Answered on March 19, 2017.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.