Compile Error: Expected End Sub

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • RobinMurphy3804
    New Member
    • Mar 2016
    • 2
    #1

    Compile Error: Expected End Sub

    I keep getting an error message for my code. I would greatly appreciate any help I can get. When someone Selects cell K74 and types in anything (usually "X"), I need a dialog box to pop up with a yes or no question. If yes, the X remains. If no, the macro will undo the X. Thank you so much for taking the time to help!

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$K$74" Then
     Sub Msgbox_Yes_No()
      Dim Response As Integer
           Response = MsgBox("Will personnel be involved with the atmospheric hazards evaluation, confined space classification, and documentation process?", Buttons:=vbYesNo)
            If Response = vbYes Then
            Exit Sub
        Else
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
        End If
    End Sub
    Last edited by Stewart Ross; Apr 1 '16, 12:44 PM. Reason: Added code tags for you
    Tags: None
    • Stewart Ross
      Recognized Expert Moderator Specialist
      • Feb 2008
      • 2545
      #2
      You have mistakenly defined a sub within a sub, hence your error message. Remove line 3 in the code above (Sub Msgbox_Yes_No() ) - it is not required and is the source of the specific error you mention. Not that you needed to, but please note that you cannot nest a sub or function definition within another sub or function - it is not valid syntax.

      You will also need to add an End If to complete your first IF statement (line 2), otherwise you'll then get an error relating to the IF not being matched to an End If.

      I'd strongly suggest you look critically at how you are structuring your work - you should be able to see at a glance the way each If matches to its Else and End If statements, and how the inner blocks are structured.

      Also, try compiling your code after you make changes. The debugger will throw errors when it sees problems, and you really need to fix these before trying to move on.

      -Stewart
      Last edited by Stewart Ross; Apr 1 '16, 12:54 PM.

        Comment

        • RobinMurphy3804
          New Member
          • Mar 2016
          • 2
          #3
          Thank you so much for your help and advice! I have never worked with code before and am having to learn as I go.

            Comment

            Previous template Next
            Working...