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