UserForm : Update List is not working : Solved by ( jaslake)

I want to update automatically list but there is error :

Compile error:

Block if without End If

Private Sub ComboBox1_Change()
Dim NewEntry As String

If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub

If Not Intersect(Target, Range("a1:a11")) Is Nothing Then
    NewEntry = ""
    NewEntry = Target
   If WorksheetFunction.CountIf(Sheet1.Range("data"), NewEntry) = 0 Then
      Application.EnableEvents = False 'Prevent Change Event Firing again while code is running.
      'Ask if they wish to add the name or not.
          iReply = MsgBox("The name " & Target & _
                   " is not part of the list, do you wish to add it.", _
                    vbYesNoCancel + vbQuestion, "HaroonSid.com")
            If iReply = vbCancel Then 'Cancelled so restore orginal text
               Target = strOriginalEntry
            ElseIf iReply = vbNo Then
            'Don't add to list. That is do nothing
            Else
            Application.EnableEvents = True

TextBox1.Text = Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("A1:g11"), 2, 0)
TextBox2.Text = Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("A1:g11"), 3, 0)
TextBox3.Text = Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("A1:g11"), 4, 0)
TextBox4.Text = Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("A1:g11"), 5, 0)
TextBox5.Text = Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("A1:g11"), 6, 0)
TextBox6.Text = Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("A1:g11"), 7, 0)

End Sub

Private Sub CommandButton1_Click()
Dim ctl
    For Each ctl In Me.Controls
        If TypeOf ctl Is Msforms.TextBox Then
            ctl.Text = ""
        End If
    Next ctl
End Sub

Private Sub CommandButton2_Click()

rownum = Application.WorksheetFunction.Match(ComboBox1.Text, Range("a:a"), 0) - 1
Range("a1").Offset(rownum, 1) = TextBox1.Text

rownum = Application.WorksheetFunction.Match(ComboBox1.Text, Range("a:a"), 0) - 1
Range("a1").Offset(rownum, 2) = TextBox2.Text

rownum = Application.WorksheetFunction.Match(ComboBox1.Text, Range("a:a"), 0) - 1
Range("a1").Offset(rownum, 3) = TextBox3.Text

rownum = Application.WorksheetFunction.Match(ComboBox1.Text, Range("a:a"), 0) - 1
Range("a1").Offset(rownum, 4) = TextBox4.Text

rownum = Application.WorksheetFunction.Match(ComboBox1.Text, Range("a:a"), 0) - 1
Range("a1").Offset(rownum, 5) = TextBox5.Text

rownum = Application.WorksheetFunction.Match(ComboBox1.Text, Range("a:a"), 0) - 1
Range("a1").Offset(rownum, 6) = TextBox6.Text




MsgBox ("Record Updated")
End Sub



Private Sub CommandButton3_Click()

    Range("B2:G11").Select
    Selection.ClearContents
  Range("A1").Select
End Sub

Private Sub CommandButton4_Click()
Unload Me
End Sub

Private Sub UserForm_Activate()
    Me.ComboBox1.List = Worksheets("Data").Range("A2:A11").Value
    
    
End Sub

This compiles...

Private Sub ComboBox1_Change()
    Dim NewEntry As String

    If Target.Cells.Count > 1 Then Exit Sub
    If Target = "" Then Exit Sub

    If Not Intersect(Target, Range("a1:a11")) Is Nothing Then
        NewEntry = ""
        NewEntry = Target
        If WorksheetFunction.CountIf(Sheet1.Range("data"), NewEntry) = 0 Then
            Application.EnableEvents = False    'Prevent Change Event Firing again while code is running.
            'Ask if they wish to add the name or not.
            iReply = MsgBox("The name " & Target & _
                          " is not part of the list, do you wish to add it.", _
                            vbYesNoCancel + vbQuestion, "HaroonSid.com")
            If iReply = vbCancel Then    'Cancelled so restore orginal text
                Target = strOriginalEntry
            ElseIf iReply = vbNo Then
                'Don't add to list. That is do nothing
            Else    'Add the new name to the cell below the last name in the named range "Name_SID"
                Application.EnableEvents = True

                TextBox1.Text = Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("A1:g11"), 2, 0)
                TextBox2.Text = Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("A1:g11"), 3, 0)
                TextBox3.Text = Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("A1:g11"), 4, 0)
                TextBox4.Text = Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("A1:g11"), 5, 0)
                TextBox5.Text = Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("A1:g11"), 6, 0)
                TextBox6.Text = Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("A1:g11"), 7, 0)
            End If
        End If
    End If
End Sub

You want to add a new name to what?

Where does the new name come from?

Hi haroon2015

I rewrote the Code rather than attemp to modify yours. Your use of Target Cells is inappropriate in this instance. The Code in the attached is a modification of this

'---------------------------------------------------------------------------------------
' Module : Database Form
' DateTime : 31/08/2005 10:55. Updatede 08-02-08
' Author : Roy Cox
' Modified : jaslake 03/21/2014
' Purpose : Data entry form for Excel, with Search facility
'---------------------------------------------------------------------------------------