VBA combo box - navigate to multiple different sheets based on drop down selection
Results 1 to 6 of 6

Thread: VBA combo box - navigate to multiple different sheets based on drop down selection
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2016
    Posts
    38
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA combo box - navigate to multiple different sheets based on drop down selection

    Hello, I have 7 different sheets that I want the combo drop down box to open based on the number in cell C50 in a separate control sheet. I have the following code written although keep getting an error "Compile error: Block If without End If"

    any help is greatly appreciated!



    Sub DropDown4_Change()


    Dim intType As Integer
    intType = Range("C50").Value
    If intType = 2 Then
    Sheets("Sheet2").Visible = True
    Sheets("Sheet2").Select
    Range("A1").Select
    If intType = 3 Then
    Sheets("Sheet3").Visible = True
    Sheets("Sheet3").Select
    Range("A1").Select
    If intType = 4 Then
    Sheets("Sheet4").Visible = True
    Sheets("Sheet4").Select
    Range("A1").Select
    If intType = 5 Then
    Sheets("Sheet5").Visible = True
    Sheets("Sheet5").Select
    Range("A1").Select
    If intType = 6 Then
    Sheets("Sheet6").Visible = True
    Sheets("Sheet6").Select
    Range("A1").Select
    If intType = 7 Then
    Sheets("Sheet7").Visible = True
    Sheets("Sheet7").Select
    Range("A1").Select
    ElseIf intType = 8 Then
    Sheets("Sheet8").Visible = True
    Sheets("Sheet8").Select
    Range("A1").Select


    End If



    End Sub

  2. #2
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,381
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA combo box - navigate to multiple different sheets based on drop down selection

    Does this work?

    Code:
    Sub test()
    Dim ws As String: ws = "Sheet" & Range("C50").Value
    
    Sheets(ws).Visible = True
    Sheets(ws).Select
    Sheets(ws).Range("A1").Select
    
    End Sub
    Last edited by lrobbo314; Aug 5th, 2019 at 09:23 PM.
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  3. #3
    New Member
    Join Date
    Nov 2016
    Posts
    38
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA combo box - navigate to multiple different sheets based on drop down selection

    Quote Originally Posted by lrobbo314 View Post
    Does this work?

    Code:
    Sub test()
    Dim ws As String: ws = "Sheet" & Range("C50").Value
    
    Sheets(ws).Visible = True
    Sheets(ws).Select
    Sheets(ws).Range("A1").Select
    
    End Sub
    No unfortunately this code did not work received subscript our of range message

  4. #4
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,381
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA combo box - navigate to multiple different sheets based on drop down selection

    What do you have in cell C50? That error means that it's looking for a sheet named Sheet#, where # is whatever is in cell C50. So, I have 2 in cell C50, and the code finds 'Sheet2', makes it visible and activates it.
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  5. #5
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,381
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA combo box - navigate to multiple different sheets based on drop down selection

    You can try this as well.

    Code:
    Sub testII()
    Dim intType As Integer: intType = Range("C50").Value
    Dim ws As Worksheet
    
    Select Case intType
        Case 2
            Set ws = Sheets("Sheet2")
        Case 3
            Set ws = Sheets("Sheet3")
        Case 4
            Set ws = Sheets("Sheet4")
        Case 5
            Set ws = Sheets("Sheet5")
        Case 6
            Set ws = Sheets("Sheet6")
        Case 7
            Set ws = Sheets("Sheet7")
        Case 8
            Set ws = Sheets("Sheet8")
    End Select
    
    ws.Visible = True
    ws.Select
    Range("A1").Select
        
    End Sub
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  6. #6
    New Member
    Join Date
    Nov 2016
    Posts
    38
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA combo box - navigate to multiple different sheets based on drop down selection

    Quote Originally Posted by lrobbo314 View Post
    You can try this as well.

    Code:
    Sub testII()
    Dim intType As Integer: intType = Range("C50").Value
    Dim ws As Worksheet
    
    Select Case intType
        Case 2
            Set ws = Sheets("Sheet2")
        Case 3
            Set ws = Sheets("Sheet3")
        Case 4
            Set ws = Sheets("Sheet4")
        Case 5
            Set ws = Sheets("Sheet5")
        Case 6
            Set ws = Sheets("Sheet6")
        Case 7
            Set ws = Sheets("Sheet7")
        Case 8
            Set ws = Sheets("Sheet8")
    End Select
    
    ws.Visible = True
    ws.Select
    Range("A1").Select
        
    End Sub
    This worked - thank you very much!!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •