Form not writing rows after deleting test rows

[StopItPoppet]

Hi, as per the subject, i created a form using VBA and eventually got everything to work. Tested it a few times and then deleted the test rows. Now my form will not add new entries.

 

[MisterProzilla]

Hi, could you add the code here and maybe a bit more info & context?

 

[dmt32]

Hi,
post the code you are having problems with would be helpful

Dave

 

[StopItPoppet]

Still learning so code a bit rough.. here it goes
Private Sub cmdCancel_Click()
Unload Me
End Sub


Private Sub cmdSave_Click()
Dim RowCount As Long
If Me.txtFirstName.Value = "" Then
MsgBox "Please Enter Required Field.", vbExclamation, "Applicant"
Me.txtFirstName.SetFocus
Exit Sub
End If
If Me.txtSurname.Value = "" Then
MsgBox "Please Enter Required Field.", vbExclamation, "Applicant"
Me.txtSurname.SetFocus
Exit Sub
End If
If Me.txt1.Value = "" Then
MsgBox "Please Enter Required Field.", vbExclamation, "Applicant"
Me.txt1.SetFocus
Exit Sub
End If
If Me.TxtMarkedby.Value = "" Then
MsgBox "Please Enter Required Field.", vbExclamation, "Applicant"
Me.TxtMarkedby.SetFocus
Exit Sub
End If
If Me.cboQualification.Value = "" Then
MsgBox "Please Enter Required Field.", vbExclamation, "Applicant"
Me.cboQualification.SetFocus
Exit Sub
End If
If Me.cboLocation.Value = "" Then
MsgBox "Please Enter Required Field.", vbExclamation, "Applicant"
Me.cboLocation.SetFocus
Exit Sub
End If
If Me.cboChannel.Value = "" Then
MsgBox "Please Enter Required Field.", vbExclamation, "Applicant"
Me.cboChannel.SetFocus
Exit Sub
End If
If Me.txtPartA.Value = "" Then
MsgBox "Please Enter Required Field.", vbExclamation, "Applicant"
Me.txtPartA.SetFocus
Exit Sub
End If
If Me.txtPartB.Value = "" Then
MsgBox "Please Enter Required Field.", vbExclamation, "Applicant"
Me.txtPartB.SetFocus
Exit Sub
End If
If Me.cboNationality.Value = "" Then
MsgBox "Please Enter Required Field.", vbExclamation, "Applicant"
Me.cboNationality.SetFocus
Exit Sub
End If
If Me.cboMethod.Value = "" Then
MsgBox "Please Enter Required Field.", vbExclamation, "Applicant"
Me.cboMethod.SetFocus
Exit Sub
End If
'write data to worksheet
RowCount = Worksheets("DATABASE").Range("A1").CurrentRegion.Rows.Count
With Worksheets("DATABASE").Range("A1")
.Offset(RowCount, 0).Value = Me.txtFirstName.Value
.Offset(RowCount, 1).Value = Me.TxtMiddle.Value
.Offset(RowCount, 2).Value = Me.txtSurname.Value
.Offset(RowCount, 3).Value = Me.txt1.Value
.Offset(RowCount, 4).Value = Me.txt2.Value
.Offset(RowCount, 5).Value = Me.txt3.Value
.Offset(RowCount, 6).Value = Me.TxtMarkedby.Value
.Offset(RowCount, 7).Value = Me.cboQualification.Value
.Offset(RowCount, 8).Value = Me.cboLocation.Value
.Offset(RowCount, 9).Value = Me.cboChannel.Value
.Offset(RowCount, 10).Value = Me.cboNationality.Value
.Offset(RowCount, 11).Value = Me.txtVD.Value
.Offset(RowCount, 12).Value = Me.txtComments.Value
.Offset(RowCount, 13).Value = Me.cboMethod.Value
.Offset(RowCount, 14).Value = Me.txtPartA.Value
.Offset(RowCount, 15).Value = Me.txtPartB.Value


End With
'clear form
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl


End Sub


Private Sub Label1_Click()


End Sub


Private Sub lblDateOfAssessment_Click()


End Sub

 

[dmt32]

Hi,
try changing this part of your code:

'write data to worksheet
RowCount = Worksheets("DATABASE").Range("A1").CurrentRegion.Rows.Count
With Worksheets("DATABASE").Range("A1")
.Offset(RowCount, 0).Value = Me.txtFirstName.Value
.Offset(RowCount, 1).Value = Me.TxtMiddle.Value
.Offset(RowCount, 2).Value = Me.txtSurname.Value
.Offset(RowCount, 3).Value = Me.txt1.Value
.Offset(RowCount, 4).Value = Me.txt2.Value
.Offset(RowCount, 5).Value = Me.txt3.Value
.Offset(RowCount, 6).Value = Me.TxtMarkedby.Value
.Offset(RowCount, 7).Value = Me.cboQualification.Value
.Offset(RowCount, 8).Value = Me.cboLocation.Value
.Offset(RowCount, 9).Value = Me.cboChannel.Value
.Offset(RowCount, 10).Value = Me.cboNationality.Value
.Offset(RowCount, 11).Value = Me.txtVD.Value
.Offset(RowCount, 12).Value = Me.txtComments.Value
.Offset(RowCount, 13).Value = Me.cboMethod.Value
.Offset(RowCount, 14).Value = Me.txtPartA.Value
.Offset(RowCount, 15).Value = Me.txtPartB.Value
End With

to this:

With ThisWorkbook.Worksheets("DATABASE")
        RowCount = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(RowCount, 1).Value = Me.txtFirstName.Value
        .Cells(RowCount, 2).Value = Me.TxtMiddle.Value
        .Cells(RowCount, 3).Value = Me.txtSurname.Value
        .Cells(RowCount, 4).Value = Me.txt1.Value
        .Cells(RowCount, 5).Value = Me.txt2.Value
        .Cells(RowCount, 6).Value = Me.txt3.Value
        .Cells(RowCount, 7).Value = Me.TxtMarkedby.Value
        .Cells(RowCount, 8).Value = Me.cboQualification.Value
        .Cells(RowCount, 9).Value = Me.cboLocation.Value
        .Cells(RowCount, 10).Value = Me.cboChannel.Value
        .Cells(RowCount, 11).Value = Me.cboNationality.Value
        .Cells(RowCount, 12).Value = Me.txtVD.Value
        .Cells(RowCount, 13).Value = Me.txtComments.Value
        .Cells(RowCount, 14).Value = Me.cboMethod.Value
        .Cells(RowCount, 15).Value = Me.txtPartA.Value
        .Cells(RowCount, 16).Value = Me.txtPartB.Value
    End With

and see if resolves your issue


Dave

 

[StopItPoppet]

Hi Dave

Thanks for the quick response. Still having an issue with new entries being saved. This only started happening after i deleted the test rows from the database sheet. Before that it worked fine.

 

[dmt32]

It was just a guess as both versions should work ok - To really understand what is going on helpful if could see copy of your workbook - Are you able to place copy of your workbook in a dropbox & provide link to it here?


Dave

 

[Fluff]

What is the value of RowCount when you run the macro?