CODEWITHSUNDEEP
excelvba
Sofia O
Sofia O

Reputation: 23

Expected End Sub

I am trying to run a macros in VBA to select something from a drop down and then select the corresponding sheet. I keep getting the compile error expected end sub. 

Sub Macro3()
'
' Macro3 Macro
'

'
Public Sub worksheet_change(ByVal target As Range)
    If target.Address = Range("Select.Type").Address Then
        Select Case target.Value
            Case "M/M/1/GD/INF/INF"
                Sheets("M-M-1-GD-INF-INF").Select
            Case "M/M/1/GD/C/INF"
                Sheets("M-M-1-GD-C-INF").Select
            Case "M/M/S/GD/INF/INF"
                Sheets("M-M-S-GD-INF-INF").Select
            Case "M/M/R/GD/K/K"
                Sheets("M-M-R-GD-K-K").Select
            Case "Closed Queuing Network"
                Sheets("CQN").Select
        End Select
    End If
End Sub

Upvotes: 0

Views: 73

Answers (2)

Dominik
Dominik

Reputation: 188

Your code works fine. But in line 1 you wrote:

`Sub Macro3()

To change into a comment please use the sign:

'

and not the sign

´

So it should be:

'Sub Macro3()

But why you don't delete `Sub Macro3(). It's not needed. Your procedure starts with: 

Public Sub worksheet_change(ByVal target As Range)

I don't know, if you know to put your procedure not in a module. You need to put it into the code from the sheet, where your dropdown-list is. There you don't need to run the macro. It runs permanently by itself, if a cell in this sheet is changed. (Double click on the sheet on the left side in the visual basic editor and include there your code)

I hope this will help.

Upvotes: 1

MZG
MZG

Reputation: 51

You have a sub which is named macro3. This sub has a missing end sub statement. 

Sub Macro3()
'
' Macro3 Macro
'
End Sub
'
Public Sub worksheet_change(ByVal target As Range)
    If target.Address = Range("Select.Type").Address Then
        Select Case target.Value
            Case "M/M/1/GD/INF/INF"
                Sheets("M-M-1-GD-INF-INF").Select
            Case "M/M/1/GD/C/INF"
                Sheets("M-M-1-GD-C-INF").Select
            Case "M/M/S/GD/INF/INF"
                Sheets("M-M-S-GD-INF-INF").Select
            Case "M/M/R/GD/K/K"
                Sheets("M-M-R-GD-K-K").Select
            Case "Closed Queuing Network"
                Sheets("CQN").Select
        End Select
    End If
End Sub

Upvotes: 0

Related Questions