Userform adding to Dynamic Named Range

[Pacman52]

Hi guys, I am trying to create a userform so that users can add a name to a Dynamic Named range called 'StaffNamesDynamic'

So for example:


In sheet 1 A3:A102 the user can select a name from a drop down list populated from the Validation List Sheet. If the name is not in the list I want them to be able to right click and a userform to show so that they can add the new name to the list.

I have already created the userform but have no idea how to code it so that it can add the new name to the next available row on the validation list.

Lastly I also have another userform working off the right click event how do I get them both to work dependant on what cell is right clicked ? I have tried inserting ElseIf or Else but keep getting a compile error code is below.

Many thanks for any advice or solutions offered.

Paul

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Set Target = Target.Cells(1, 1)
    If Not Intersect(Target, Range("D3:D102")) Is Nothing Then
        Application.EnableEvents = False
        Cancel = True
        Application.Dialogs(xlDialogPatterns).Show
    End If
    Application.EnableEvents = True

    If Target.Column <> 21 Then Exit Sub
    Cancel = True
    xRow = Target.Row
    strCellText = Target.Value
    UserForm1.Show
    
 
    If Target.Column <> 6 Then Exit Sub
    Cancel = True
    xRow = Target.Row
    strCellText = Target.Value
    UserForm2.Show
    
End Sub