UserForm Data Not Connecting to Table!

[kccushman]

Hello, I am not a genius in Excel and VBA but I'm definitely new to 2016 and it's been a long time. My new job has me creating them an Excel spreadsheet and a form that allows us to enter new data. Could you all help?

I have a Userform created and a button that allows us to open it. I cannot figure out how to get all my data to sync from the form to the table. What am I doing wrong. Here's my code. It will not add data to the table no matter what I fill in or don't fill in. Help please!

Private Sub cmdAdd_Click()
Dim RowCount As Long
RowCount = Worksheets("PBP Tracking Sheet").Range("A1").CurrentRegion.Rows.Count
With Worksheets("PBP Tracking Sheet").Range("A1")
.Offset(RowCount, 1).Value = Me.CheckBox1.Value
.Offset(RowCount, 3).Value = Me.TextBox2.Value
.Offset(RowCount, 4).Value = Me.TextBox4.Value
.Offset(RowCount, 5).Value = Me.TextBox3.Value
.Offset(RowCount, 6).Value = Me.TextBox5.Value
.Offset(RowCount, 7).Value = Me.TextBox1.Value
.Offset(RowCount, 8).Value = Me.TextBox6.Value
.Offset(RowCount, 9).Value = Me.TextBox7.Value
.Offset(RowCount, 10).Value = Me.TextBox8.Value
.Offset(RowCount, 11).Value = ComboBox1.Value
.Offset(RowCount, 12).Value = Me.TextBox9.Value
.Offset(RowCount, 13).Value = Me.TextBox10.Value
.Offset(RowCount, 14).Value = Me.TextBox27.Value
.Offset(RowCount, 15).Value = Me.TextBox26.Value
.Offset(RowCount, 16).Value = Me.TextBox25.Value
.Offset(RowCount, 17).Value = Me.TextBox15.Value
.Offset(RowCount, 18).Value = Me.ComboBox2.Value
.Offset(RowCount, 19).Value = Me.ComboBox3.Value
.Offset(RowCount, 20).Value = Me.TextBox17.Value
.Offset(RowCount, 21).Value = Me.TextBox18.Value
.Offset(RowCount, 22).Value = Me.TextBox19.Value
.Offset(RowCount, 23).Value = Me.TextBox20.Value
.Offset(RowCount, 24).Value = Me.TextBox21.Value
.Offset(RowCount, 25).Value = Me.TextBox22.Value
.Offset(RowCount, 26).Value = Me.TextBox23.Value
.Cells(lRow, 27).Value = Me.TextBox16.Value
If Me.CheckBox1.Value = True Then
.Offest(RowCount, 0).Value = "Yes"
Else
.Offset(RowCount, 0).Value = "No"
If Me.CheckBox2.Value = True Then
.Offest(RowCount, 1).Value = "Yes"
Else
.Offset(RowCount, 1).Value = "No"
End With

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


Private Sub CommandButton2_Click()
Unload Me
End Sub

 

[Norie]

Are you sure no data is being added to the sheet?

It could be getting added but not where you expect it to be added, maybe take a look further down the sheet.

 

[kccushman]

Thanks Norie, but It's not on there at all. I looked!

 

[Norie]

Is the code definitely being executed?

 

[kccushman]

Yes, because the code related to the cmd button is working to close the form.

 

[Norie]

Eh, just because that code is being executed doesn't mean any other code is being executed.

Try setting up a breakpoint in the cmdAdd_Click sub and see if you enter debug when you click the button.

 

[kccushman]

I tried it and it's STILL not working. I cannot figure out what I'm doing wrong.

 

[Norie]

I can't see anything obviously wrong with the code.

Could you upload a sample of the workbook to a file-sharing site like Box.net and post a link?

 

[kccushman]

Okay, I'm getting a Compile Error: End With without With.

 

[kccushman]

Here's what I changed and now I'm getting a new error.

Private Sub CommandButton1_Click()
Dim RowCount As Long
RowCount = Worksheets("Sheet4").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Sheet4").Range("A1")
.Offset(RowCount, 3).Value = Me.TextBox2.Value
.Offset(RowCount, 4).Value = Me.TextBox4.Value
.Offset(RowCount, 5).Value = Me.TextBox3.Value
.Offset(RowCount, 6).Value = Me.TextBox5.Value
.Offset(RowCount, 7).Value = Me.TextBox1.Value
.Offset(RowCount, 8).Value = Me.TextBox6.Value
.Offset(RowCount, 9).Value = Me.TextBox7.Value
.Offset(RowCount, 10).Value = Me.TextBox8.Value
.Offset(RowCount, 11).Value = ComboBox1.Value
.Offset(RowCount, 12).Value = Me.TextBox9.Value
.Offset(RowCount, 13).Value = Me.TextBox10.Value
.Offset(RowCount, 14).Value = Me.TextBox27.Value
.Offset(RowCount, 15).Value = Me.TextBox26.Value
.Offset(RowCount, 16).Value = Me.TextBox25.Value
.Offset(RowCount, 17).Value = Me.TextBox15.Value
.Offset(RowCount, 18).Value = Me.ComboBox2.Value
.Offset(RowCount, 19).Value = Me.ComboBox3.Value
.Offset(RowCount, 20).Value = Me.TextBox17.Value
.Offset(RowCount, 21).Value = Me.TextBox18.Value
.Offset(RowCount, 22).Value = Me.TextBox19.Value
.Offset(RowCount, 23).Value = Me.TextBox20.Value
.Offset(RowCount, 24).Value = Me.TextBox21.Value
.Offset(RowCount, 25).Value = Me.TextBox22.Value
.Offset(RowCount, 26).Value = Me.TextBox23.Value
.Cells(lRow, 27).Value = Me.TextBox16.Value


If Me.CheckBox1.Value = True Then
.Offest(RowCount, 0).Value = "Yes"
Else
.Offset(RowCount, 0).Value = "No"
If Me.CheckBox2.Value = True Then
.Offest(RowCount, 1).Value = "Yes"
Else
.Offset(RowCount, 1).Value = "No"
End If


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