VBA for Go to Selected worksheet and hide rest others

R

Rahul Malhotra

New member
Joined
Apr 18, 2018
Messages
43
Reaction score
0
Points
0
Excel Version(s)
2013, 2016
Hi, I am looking for the VBA Script to go to selected worksheet from combobox and rest other inactive worksheets will be hidden immediately. Only the active worksheet which is selected from dropdown will be visible.

Any help would be highly appreciated. Thanks in Advance.

Rahul
 
.
Code: 
Sub SheetHidden()
    Dim ws As Worksheet
    Dim sName As String
    sName = Application.InputBox("Range", Application.ActiveSheet.Name, Type: = 2)
    For Each ws In Application.ActiveWorkbook.Worksheets
        If ws.Name <> sName Then
            ws.Visible = xlSheetHidden
        End If
    Next
End Sub
 
Hi Logit, I have given the Range from Sheet5 Cell AC2:AC24 but the script pop up the range screen on every selection from the combobox and the script getting debug at "ws.Visible = xlSheetHidden".
 
.
Post your workbook with before and after expectations.
 
Hi Logit, surely i will provide the sample workbook but for providing the sample workbook i do need some time to create as per my expected results.
 
Hi Logit, Please find here attached Sample workbook, where if i do select any option from the dropdown then the Range screen pop up. I want to keep every worksheet veryhidden and according to the combobox list the choosen worksheet want to make as visible and rest all others will be veryhidden.

Is it possible to go the selected worksheet without taking any time would be a great support.

View attachment Sample workbook to go selected worksheet.xlsb
 
Logit said:
.
Post your workbook with before and after expectations.
Click to expand...

Hi Logit, I have post my sample workbook. Please help me as i am breaking my head on this since long time at google but nothing has come out yet.
 
.
Code: 
Option Explicit


Private Sub ComboBox1_Change()
    Dim actWsh As String
    Dim ws As Worksheet
    actWsh = ComboBox1.Text
    For Each ws In Application.ActiveWorkbook.Worksheets
        If ws.Name <> "Sheet5" Then
            ws.Visible = xlSheetVeryHidden
        End If
    Next
    If actWsh <> "Sheet5" Then
        Worksheets(actWsh).Visible = True
        Worksheets(actWsh).Select
    End If
End Sub
 
@ Rahul
does this work for you?

in This Workbook module
Code: 
Private Sub Workbook_Open()
    Dim i As Integer

Sheets("Sheet5").Visible = xlSheetVisible

For i = 1 To ThisWorkbook.Sheets.Count
    If Sheets(i).Name <> "Sheet5" Then
        Sheets(i).Visible = xlSheetHidden
    End If
Next i

End Sub

in the sheet5 module
Code: 
Private Sub ComboBox1_Change()
    
If ComboBox1.Value = "" Then
    Exit Sub
Else
    Sheets(ComboBox1.Value).Visible = xlSheetVisible
    Sheets("Sheet5").Visible = xlSheetHidden
    ComboBox1.Value = ""
End If

End Sub

in ComboBox1 properties
include the blank cell K2 in the ListFillRange
remove the LinkedCell as it's not required
 

Attachments

  • Sample workbook to go selected worksheet.xlsm
    31.2 KB · Views: 18
Thank you so much Logit and NoS both scripts worked like a charm. Is there any way where i can say Thank you or Mark Reputation and close the thread marking a solved.
 
You must log in or register to reply here.
Back
Top