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

[Mariyka1]

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

 

[lrobbo314]

Does this work?

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

 

[Mariyka1]

Does this work?

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

 

[lrobbo314]

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.

 

[lrobbo314]

You can try this as well.

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

 

[Mariyka1]

You can try this as well.

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