A Macro That Unhide and Going a Specific Sheet

[Unexpc]

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...

 

[maabadi]

This macro hides all sheets except active sheet.

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

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

 

[Unexpc]

This macro hides all sheets except active sheet.

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

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

 

[maabadi]

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.

 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))

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

 

[Unexpc]

this for first code

 

[Unexpc]

this for second

 

[Unexpc]

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.

 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))

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

 

[Unexpc]

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...

 

[maabadi]

For 3rd Macro this is guides:
if need at each stage, change name to nemes at images.

 

[Unexpc]

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