Show Specific Sheets in Excel With Drop Down List

**sandy1977** · 09-27-2023, 05:02 PM

Good day!!

I'm having trouble trying to open a specific worksheet that is selected from a dropdown list. The goal is to select one of the 32 options from the dropdown list and have excel show me only that worksheet. The issue with the code I currently have is that its not showing the selected worksheet because the name of the different options in the dropdown list are too long. I guess the solution would be to create a VBA code that reads the initial number of the string and then show that specific worksheet. For example: If I select "1.8 Citizen petitioner One Step Child" from the drop down list, have excel only read the initial number (as string) and only show that worksheet name, which would be worksheet named: 1.8.

I have attached the workbook to better understand what I'm trying to explain.

Thanks in advance for any help in this matter!!

Please Login or Register  to view this content.

**kvsrinivasamurthy** · 09-27-2023, 06:22 PM

Try

Please Login or Register  to view this content.

**sandy1977** · 09-27-2023, 06:46 PM

Hello, thanks for your reply, but it doesn't open the worksheet selected from the dropdown menu.

**Croweater** · 09-27-2023, 09:01 PM

A couple of minor changes.

1. You need a space after the worksheet name, otherwise you'll make Sheet 1.1 visible when you select 1.11,1.12 etc.
2. You need to Activate your sheet

Please Login or Register  to view this content.

I've also noticed that you have a case mismatch for " ALL"/" All"

That should be rectified in this bit of code

Please Login or Register  to view this content.

**bebo021999** · 09-27-2023, 11:44 PM

In sheet "MENU" 's module

PHP Code:


Option Explicit 
Private Sub Worksheet_Change(ByVal Target As Range) 
If Target.Address = Range("SelectType").Address Then 
    Select Case Target.Value 
        Case " ALL" 
            ShowAllSheets 
        Case "" 
        Case Else 
            ShowSelSheets 
    End Select 
End If 
End Sub

In General module

PHP Code:


Option Explicit 
Sub ShowAllSheets() 
Dim ws As Worksheet 
For Each ws In ActiveWorkbook.Sheets 
  ws.Visible = xlSheetVisible 
Next ws 
End Sub 
Sub ShowSelSheets() 
Dim ws As Worksheet 
Dim strType As String 
strType = Worksheets("Menu").Range("SelectType").Value 
For Each ws In ActiveWorkbook.Sheets 
    ws.Visible = xlSheetHidden 
    If Split(strType)(0) = ws.Name Or ws.Name = "Menu" Then ws.Visible = xlSheetVisible 
Next 
End Sub

**jindon** · 09-28-2023, 01:16 AM

sandy1977,

1) To Menu sheet code module.

Please Login or Register  to view this content.

2) Module1 code.

Please Login or Register  to view this content.

**Croweater** · 09-28-2023, 03:20 AM

Originally Posted by bebo021999

Quang PT
Inbox me for: tutoring - more complex requests.
paypal: [email protected]
https://www.facebook.com/ThuThuatExcelQuanTriKetoan

I almost missed this in the 'fine print', having been overcome by the stunning innovation and unprecedented wisdom of the previous two posts, however,
I assume that now we are permitted to advertise for professional services on this forum?

I must have missed this change to forum rules. Could the admins/mods please enlighten me as to when this happened, as I would like to offer my professional services in the
spirit of free enterprise and open competition.

So much for the oft paraphrased 'we are all unpaid volunteers on this site'.