UserForm Data Not Connecting to Table!

K

kccushman

New Member
Joined
Jun 20, 2017
Messages
13
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
 
Sort by date Sort by votes
Eh, I'm confused.:eek:

I thought you weren't getting any errors.
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Try this.
Code: 
Private Sub CommandButton1_Click()
Dim ctl As Control
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"
        End If

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

    End With
    
    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
 
Upvote 0
I tried moving things around and it then it gave me error messages. Sorry!

I tried it and it says run-time error '9':
Subscript out of range
 
Upvote 0
Clicked debug and it highlights this:
RowCount = Worksheets("Sheet4").Range("A1").CurrentRegion.Rows.Count
 
Upvote 0
Try replacing Worksheets("Sheet4") with either Sheet4 or Worksheets("PBP Tracking Sheet").
Code: 
Private Sub CommandButton1_Click()
Dim ctl As Control
Dim RowCount As Long

    RowCount = Worksheets("PBP Tracking Sheet").Range("A1").CurrentRegion.Rows.Count

    With Worksheets("PBP Tracking Sheet").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(RowCount, 27).Value = Me.TextBox16.Value

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

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

    End With
    
    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
 
Upvote 0
Okay, new error messages now:

Run-time error '438':
Object doesn't support this property or method.

Debug: the below line shows up.
.Offest(RowCount, 0).Value = "Yes"
 
Upvote 0
I think I may need to stop messing around in the code. Oh goodness! Thank you for catching that. :LOL:

IT WORKS! Norie, you are my hero! Thank you SO much.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
User Form Textbox looping
2
Replies
12
Views
586
sjtjr73
S
E
  • Question
If textbox is empty, do not copy the value to worksheet
Replies
1
Views
245
NoSparks
N
A
  • Question
Code to Add Data From UserForm In Row Above or Below Based on Criteria
Replies
1
Views
218
amoverton2
A
I
  • Question
Userform to show error message as opposed 0 in textbox
Replies
2
Views
262
ipbr21054
I
R
  • Question
VBA textbox not updating Unique ID
Replies
1
Views
537
Saurabhj
Saurabhj

Forum statistics

Threads
1,214,991
Messages
6,122,628
Members
449,095
Latest member
bsb1122

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top