Userform is overwriting line of data

[manny88]

My userform is overwriting the existing line of data. I have 650 lines of data and with the code below it is overwriting what is in line 650 instead of adding into the next empty row. There are also prompts to make sure no fields are left blank and no duplicates are added, but they are not working either now. Completely stumped!

I know the code is messy. I'm not great with vba. This only became an issue when I added an additional unserform to find and update certain cells of of an existing entry.

Private Sub OKButton_Click()
 
 
If Trim(NameTextBox.Value) = "" And Me.Visible Then
       MsgBox "Name is required.", vbExclamation
       NameTextBox.SetFocus
       NameTextBox.BackColor = vbYellow
    Exit Sub
   
    ElseIf Trim(IDtxtBox.Value) = "" And Me.Visible Then
        MsgBox "ID is required.", vbExclamation
        IDtxtBox.SetFocus
        IDtxtBox.BackColor = vbYellow
    Exit Sub
   
    ElseIf Trim(DateTextBox.Value) = "" And Me.Visible Then
        MsgBox "Date is required.", vbExclamation
        DateTextBox.SetFocus
        DateTextBox.BackColor = vbYellow
    Exit Sub
   
    ElseIf TypeComboBox.Value = "" Then
        MsgBox "Type is required.", vbExclamation
        TypeComboBox.SetFocus
        TypeComboBox.BackColor = vbYellow
    Exit Sub
   
    ElseIf SGComboBox.Value = "" Then
        MsgBox "SG field is required.", vbExclamation
        SGComboBox.SetFocus
        SGComboBox.BackColor = vbYellow
    Exit Sub
   
    ElseIf MailComboBox.Value = "" Then
        MsgBox "Mail field is required.", vbExclamation
        MailComboBox.SetFocus
        MailComboBox.BackColor = vbYellow
    Exit Sub
   
    ElseIf AMcomboBox.Value = "" Then
        MsgBox "AM field is required.", vbExclamation
        AMcomboBox.SetFocus
        AMcomboBox.BackColor = vbYellow
    Exit Sub
   
End If
 
'Duplicate code - checks ID Column B in Admin Sheet
 
If WorksheetFunction.CountIf(Worksheets("Admin").Columns(2), IDtxtBox) > 0 Then
MsgBox IDtxtBox.Value & " has already been submitted.", vbCritical
Exit Sub
End If
 
'Rest Of CommandButton Code Here !!!!
 
    With ThisWorkbook
        If .MultiUserEditing Then
            .AcceptAllChanges
            .Save
        End If
    End With

    With ThisWorkbook
        If .MultiUserEditing Then
            .AcceptAllChanges
        End If
        .Save
    End With
 
Dim emptyRow As Long
 
'Make Sheet1 active
Sheet1.Activate
 
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
 
'Transfer information
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = IDtxtBox.Value
With DateTextBox
    If IsDate(.Value) Then
        Cells(emptyRow, 3).Value = DateValue(.Value)
    Else
        Cells(emptyRow, 3).Value = .Value
        End If
    End With
Cells(emptyRow, 4).Value = TypeComboBox.Value
Cells(emptyRow, 5).Value = AMcomboBox.Value
Cells(emptyRow, 6).Value = SGComboBox.Value
Cells(emptyRow, 7).Value = VDEComboBox.Value
With Cells(emptyRow, 8)
    .Value = Now()
    .NumberFormat = "dd/mm/yyyy HH:mm"
End With
        Application.DisplayAlerts = True
        ThisWorkbook.Save
 
 
' ------------------------- Below code to prompt phase 2 ----------------------------
 
 
Dim msg, Style, Response
 
msg = "Your entry has been successfully saved!" & vbNewLine & vbNewLine & "Do you wish to submit another?"   'Define Message
    Style = vbYesNo + vbQuestion 'Define Buttons
    Response = MsgBox(msg, Style)
If Response = vbYes Then 'User chose Yes
    Call UserForm_Initialize
 
Else  'User chose No. Yes = Save File and Exit document // No = Exit without saving document // Cancel = Return to userform
 
msg = "Do you wish to exit the document?"
 
Select Case MsgBox(msg, vbYesNo + vbQuestion)
    Case vbYes
        Application.DisplayAlerts = False
        ThisWorkbook.Close
 
    Case vbNo
        Call UserForm_Initialize
       
        
End Select
 
End If
 
End Sub
 
Private Sub UserForm_Initialize()
 
'Empty NameTextBox - Disabled text box editing in textbox property
NameTextBox.Value = Application.UserName
 
'Empty IDtxtBox
IDtxtBox.Value = ""
 
'Empty DateTextBox
DateTextBox.Value = ""
 
'Empty TypeComboBox
TypeComboBox.Clear
 
'Fill TypeComboBox
With TypeComboBox
    .AddItem "Admission"
    .AddItem "Delay"
    .AddItem "Removed"
End With
 
'Empty AM ComboBox
AMcomboBox.Clear
 
'Fill AMComboBox
With AMcomboBox
    .AddItem "A"
    .AddItem "M"
End With
 
'Empty SGComboBox
SGComboBox.Clear
 
'Fill SGComboBox
With SGComboBox
    .AddItem "Not Applicable"
    .AddItem "Approved"
End With
 
'Empty VDEcomboBox
VDEComboBox.Clear
 
'Fill VDEComboBox
With VDEComboBox
    .AddItem "Yes"
    .AddItem "No"
End With
 
End Sub
 
Private Sub ViewRecordsButton_Click()
 
    Unload Me
    SearchForm.Show
 
End Sub

 

[Yongle]

Try

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

emptyRow = Range("A" & Rows.Count).End(xlUp).Row + 1

You should be able to work out why CountA formula is not working for you by placing this formula in another column in the sheet
=COUNTA(A:A)
If it returns a number smaller than the last used row in column A - empty cell(s) somewhere in the column???