A Macro That Unhide and Going a Specific Sheet

Unexpc

Active Member
Joined
Nov 12, 2020
Messages
496
Office Version
  1. 2019
Platform
  1. Windows
Hi guys
There is a macro that Unhide multiple sheet and Going to specific one of them and then, when deselect one of them (select another sheet from this sheets) again hidden...
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
This macro hides all sheets except active sheet.
VBA Code:
Sub Macro1()
'Step 1:  Declare your variables
    Dim ws As Worksheet
'Step 2: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets
'Step 3: Check each worksheet name
    If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
'Step 4: Hide the sheet
    ws.Visible = xlSheetHidden
    End If
'Step 5:  Loop to next worksheet
    Next ws
End Sub
and this one hide all except one that tell in InputBox
VBA Code:
Sub SheetHidden()
    Dim xWs As Worksheet
    Dim xName As String
    xName = Application.InputBox("Range", xTitleId, Application.ActiveSheet.Name, Type: = 2)
    For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name <> xName Then
            xWs.Visible = xlSheetHidden
        End If
    Next
End Sub
 
Upvote 0
This macro hides all sheets except active sheet.
VBA Code:
Sub Macro1()
'Step 1:  Declare your variables
    Dim ws As Worksheet
'Step 2: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets
'Step 3: Check each worksheet name
    If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
'Step 4: Hide the sheet
    ws.Visible = xlSheetHidden
    End If
'Step 5:  Loop to next worksheet
    Next ws
End Sub
and this one hide all except one that tell in InputBox
VBA Code:
Sub SheetHidden()
    Dim xWs As Worksheet
    Dim xName As String
    xName = Application.InputBox("Range", xTitleId, Application.ActiveSheet.Name, Type: = 2)
    For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name <> xName Then
            xWs.Visible = xlSheetHidden
        End If
    Next
End Sub
for first code, after see it, i think i need it but i set a table from sheets in one sheet that each sheets linked to specific cell of table sheet, how going to this sheets when click on?
for second code, i mean when select a macro button, unhide multiple sheets and going to a specific sheet and when select out of this multiple sheets, this sheets will be hidden again
 
Upvote 0
OR

1st - Add a combobox into your first sheet and properly name it (I called it cmbSheet). I suggest to use an ActiveX Combobox (in Excel 2007, under Developer tab).

2nd - Open VBA and add the below code into your workbook code. This code will populate the combobox with the sheet names every time the workbook is opened.

VBA Code:
 Private Sub Workbook_Open()

    Dim oSheet As Excel.Worksheet
    Dim oCmbBox As MSForms.ComboBox
    Dim xWs As Worksheet
    Dim xName As String
    Set oCmbBox = ActiveWorkbook.Sheets(1).cmbSheet

    oCmbBox.Clear

    For Each oSheet In ActiveWorkbook.Sheets

        oCmbBox.AddItem oSheet.Name

    Next oSheet
    
End Sub

3rd - Now, go to the code of your sheet (where the combobox has been added) and add the code that will activate the sheet chosen in the combobox & Active Sheet. The code is (Change cmbSheet to your combobox name, You see it when it add , right click, view code , Properties window, (Down-Left side of Vba window))
VBA Code:
Private Sub cmbsheet_Change()
   Dim xWs As Worksheet
    Dim xName As String
 Application.ScreenUpdating = False
 For Each xWs In ThisWorkbook.Worksheets
    
    xWs.Visible = True
  Next
 For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name <> cmbSheet.Value And xWs.Name <> ThisWorkbook.ActiveSheet.Name Then
            xWs.Visible = xlSheetHidden
        End If
    Next
 Application.ScreenUpdating = True
End Sub
 
Upvote 0
this for first code
 

Attachments

  • Screenshot 2020-12-10 223219.jpg
    Screenshot 2020-12-10 223219.jpg
    127.9 KB · Views: 11
  • Screenshot 2020-12-10 223228.jpg
    Screenshot 2020-12-10 223228.jpg
    99.5 KB · Views: 10
  • Screenshot 2020-12-10 232047.jpg
    Screenshot 2020-12-10 232047.jpg
    82.4 KB · Views: 9
Upvote 0
this for second
 

Attachments

  • Screenshot 2020-12-10 232411.jpg
    Screenshot 2020-12-10 232411.jpg
    165.4 KB · Views: 3
  • Screenshot 2020-12-10 232612.jpg
    Screenshot 2020-12-10 232612.jpg
    174.6 KB · Views: 3
  • Screenshot 2020-12-10 232734.jpg
    Screenshot 2020-12-10 232734.jpg
    191.9 KB · Views: 5
Upvote 0
OR

1st - Add a combobox into your first sheet and properly name it (I called it cmbSheet). I suggest to use an ActiveX Combobox (in Excel 2007, under Developer tab).

2nd - Open VBA and add the below code into your workbook code. This code will populate the combobox with the sheet names every time the workbook is opened.

VBA Code:
 Private Sub Workbook_Open()

    Dim oSheet As Excel.Worksheet
    Dim oCmbBox As MSForms.ComboBox
    Dim xWs As Worksheet
    Dim xName As String
    Set oCmbBox = ActiveWorkbook.Sheets(1).cmbSheet

    oCmbBox.Clear

    For Each oSheet In ActiveWorkbook.Sheets

        oCmbBox.AddItem oSheet.Name

    Next oSheet
  
End Sub

3rd - Now, go to the code of your sheet (where the combobox has been added) and add the code that will activate the sheet chosen in the combobox & Active Sheet. The code is (Change cmbSheet to your combobox name, You see it when it add , right click, view code , Properties window, (Down-Left side of Vba window))
VBA Code:
Private Sub cmbsheet_Change()
   Dim xWs As Worksheet
    Dim xName As String
Application.ScreenUpdating = False
For Each xWs In ThisWorkbook.Worksheets
  
    xWs.Visible = True
  Next
For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name <> cmbSheet.Value And xWs.Name <> ThisWorkbook.ActiveSheet.Name Then
            xWs.Visible = xlSheetHidden
        End If
    Next
Application.ScreenUpdating = True
End Sub
can you send a photo, i don't understand sorry:(
and please check photos that i uploaded
 
Upvote 0
this for second
For this, can create two different code, one of them just unhide sheets number 1,2 and then unhide and another just unhide sheet number 3 and then unhide...
 
Upvote 0
For 3rd Macro this is guides:
if need at each stage, change name to nemes at images.
 

Attachments

  • 111111.jpg
    111111.jpg
    102.3 KB · Views: 11
  • 222222.jpg
    222222.jpg
    184.1 KB · Views: 11
  • 33333.jpg
    33333.jpg
    136.7 KB · Views: 11
Upvote 0
i doing your tips step by step, but i don't know how does it work?
for example one of my questions, when i have 14 Sheets, i insert 14 combo box?
i have a sheet that write sheet names of workbook in specific cells and when i select this cells, after going this sheets for example cell I4 Linked to Sheet1 and after i select this cell, Sheet1 is active and going to this sheet. i want when this is happened, all sheets except two sheets or more sheets will be hidden
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top