Expected end sub ошибка vba

I am trying, when a line of information is added to one of my sheets, to automatically add that line to a different sheet in the same workbook.

I found this code and tweaked it a little:

Sub addrow()

Public Sub worksheet_change(ByVal target As Range)

    Set sourcebook = ThisWorkbook
    Set sourcesheet = sourcebook.Worksheets("sheet1")

    Set targetbook = ThisWorkbook
    Set targetsheet = targetbook.Worksheets("sheet10")

    If sourcesheet.Cells(198, 16).Value = "Auto" Or _
        sourcesheet.Cells(198, 16).Value = "Connect" Or _
        sourcesheet.Cells(198, 16).Value = "Multiple*" Or _
        sourcesheet.Cells(198, 16).Value = "Property" Or _
        sourcesheet.Cells(198, 16).Value = "Umbrella" Or _
        sourcesheet.Cells(198, 16).Value = "WC" Then
        GoTo link
    Else
        GoTo insertion
    End If

    insertion: targetsheet.Activate
    ActiveSheet.Rows(198).EntireRow.Insert

    sourcesheet.Activate

link:
    'targetsheet.Cells(194, targetsheet.Range("initial response").Column) = sourcesheet.Cells(198, 16).Value
    targetsheet.Cells(194, 16) = sourcesheet.Cells(198, 16).Value

    targetsheet.Cells(194, 16) = sourcesheet.Cells(198, 16).Value

End Sub

I get the error message «Compile Error: Expected End Sub» and it highlights that first line of code- Sub addrow(). When I try taking this line out, VBA requires me to create a new macro when I try and run it, which then adds that line back in, and I am back to square one.

• you have to close the If statement with End If After Exit Sub
• And you are calling a Sub within a Sub which throws the End Sub error you only need to put in EmailExtract

See code below:

Private Sub CommandButton1_Click()
If TextBox1.Value = "Password" Then 'Replace Password by your custom password
    EmailExtract 'This is the sub that was being called by your button.
Else
    MsgBox "You are not allowed to launch the macro"
Exit Sub
End If
End Sub

Update:

This is a different approach on password protection and simply uses an InputBox instead of an UserForm to collect and check a password value.

Make sure that you password protect your VBA code, otherwise anyone that knows how can check the code and get the password from the code.

Sub EmailExtract()
Dim Message As String, Title As String, Password As String
Message = "Enter the macro password"    ' Set prompt.
Title = "Macro Password"    ' Set title.
Password = InputBox(Message, Title)
If Password = "Password Here" Then

    ''***Code for the macro then follows***

Else
    MsgBox "You are not allowed to launch the macro"
    Exit Sub
End If
End Sub

2nd Update:

This way you create a Sub to call the UserForm then validate the password input, after that you call the sub EmailExtract() and run the desired code.

The way for using a password protection with a UserForm is as followed:

Show UserForm (being called by your shape):

Sub UserFormShow()

UserForm1.Show

End Sub

Do password validation:

Private Sub CommandButton1_Click()
If TextBox1.Value = "Password" Then 'Replace Password by your custom password
    EmailExtract 'The new sub your going to call
Else
    MsgBox "You are not allowed to launch the macro"
Exit Sub
End If

End Sub

Run your code (the new sub):

Sub EmailExtract()

***Code for the macro then follows***

end sub

1