Problems with Adding a new line to a table

[Robert_Conklin]

I have set up some command buttons that open user-forms for my end users to enter data into in order to update tabs in the workbook. I have set up three tables, one in each of the three tabs. The problem is, in two of the three tables a new line is not being created in the table. It just overwrites the lines below the table. In the other code, the same thing is happening, but it is overwriting the information in the first(top) line in the table.

My goal is when the end users clicks the submit button on the user-form, a NEW line is created in the table that copies all settings and formulae from the line above as well and write the data that was entered in to the user-form. Below is the code:

Private Sub cb01_Click()With Sheets("PGS Score Card")
    iRow = .ListObjects("PGSSC_tbl").Range.Columns(2).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    .Cells(iRow, 3).Value = tbx01.Value
    .Cells(iRow, 5).Value = tbx21.Value
    .Cells(iRow, 6).Value = tbx02.Value
    .Cells(iRow, 7).Value = tbx18.Value
End With


With Sheets("PGSSavingsTimeline(Projections)")
    iRow = .ListObjects("PGSSTP_tbl").Range.Columns(3).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    .Cells(iRow, 2).Resize(, 22).Value = Array(cbx13.Value, tbx01.Value, cbx02.Value, tbx21.Value, tbx22.Value, tbx03.Value, cbx04.Value, cbx05.Value, _
    cbx06.Value, cbx07.Value, tbx04.Value, cbx08.Value, tbx18.Value, tbx05.Value, tbx06.Value, tbx07.Value, cbx09.Value, tbx09.Value, tbx08.Value, _
    , tbx23.Value, tbx24.Value)
End With
With Sheets("PG&S Savings Timeline (Roll-up)")
    iRow = .ListObjects("PGSSTRu_tbl").Range.Columns(3).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    .Cells(iRow, 2).Resize(, 35).Value = Array(, tbx01.Value, , cbx02.Value, tbx21.Value, , , cbx10.Value, cbx12.Value, tbx10.Value, cbx13.Value, tbx11.Value, _
    cbx11.Value, tbx12.Value, , , , , , , , , , , , tbx13.Value, , tbx19.Value, tbx15.Value, , , , tbx20.Value, tbx17.Value, tbx14.Value)
End With
  For Each ctrl In Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then ctrl.Value = ""
Next ctrl
    LB_01.ListIndex = -1
    Call UserForm_Initialize
End Sub

The PGS ScoreCard tab and the PGSSavingsTimeline(Projections) tab are the two that overwrite the data in the lines below the table. The PGSSavingsTimeline(Roll-Up) tab is overwriting the data in the first (top) line of the table.

 

[Robert_Conklin]

The code I am working with in in a form called "AddNewProject". Below is the entire code for the "AddNewProject" form: The code posts all of the data to it corresponding locations on the three worksheets successfully. The only problem is, when it enters a the data on the new line of the table, it overwrites the data that is already on the next line instead of creating a new line in the table.

Option ExplicitDim iRow As Long, i As Long, j As Long
Dim ctrl As Control
Dim collist As Collection
Dim tbx As OLEObject
Dim NewRow As ListRows
Private Sub TableRowInsert()
Set NewRow = Selection.ListObject.ListRows.Add
Selection.ListObjects("PGSSC_tbl").ListRows.Add AlwaysInsert:=True
Selection.ListObjects("PGSSTP_tbl").ListRows.Add AlwaysInsert:=True
Selection.ListObjects("PGSSTRu_tbl").ListRows.Add AlwaysInsert:=True
End Sub


Private Sub cb01_Click()
With Sheets("PGS Score Card")
    iRow = .ListObjects("PGSSC_tbl").Range.Columns(2).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    .Cells(iRow, 3).Value = tbx01.Value
    .Cells(iRow, 5).Value = tbx21.Value
    .Cells(iRow, 6).Value = tbx02.Value
    .Cells(iRow, 7).Value = tbx18.Value
End With


With Sheets("PGSSavingsTimeline(Projections)")
    iRow = .ListObjects("PGSSTP_tbl").Range.Columns(3).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    .Cells(iRow, 2).Resize(, 22).Value = Array(cbx13.Value, tbx01.Value, cbx02.Value, tbx21.Value, tbx22.Value, tbx03.Value, cbx04.Value, cbx05.Value, _
    cbx06.Value, cbx07.Value, tbx04.Value, cbx08.Value, tbx26.Value, tbx05.Value, tbx06.Value, tbx07.Value, cbx09.Value, tbx09.Value, tbx08.Value, _
    tbx27.Value, tbx23.Value, tbx24.Value)
End With
With Sheets("PG&S Savings Timeline (Roll-up)")
    iRow = .ListRows("PGSSTRu_tbl").Range.Columns(3).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    .Cells(iRow, 2).Resize(, 35).Value = Array(, tbx01.Value, , , , , , cbx10.Value, cbx12.Value, tbx10.Value, cbx14.Value, tbx11.Value, _
    cbx11.Value, tbx12.Value, , , , , , , , , , , , tbx25.Value, tbx19.Value, , tbx15.Value, , , , tbx20.Value, tbx17.Value, tbx14.Value)
End With
  For Each ctrl In Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then ctrl.Value = ""
Next ctrl
    LB_01.ListIndex = -1
    Call UserForm_Initialize
End Sub
Private Sub cb03_Click()
'Clear all fields
    For Each ctrl In Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
            ctrl.Value = ""
            ctrl.BackColor = RGB(255, 255, 255)
        End If
    Next ctrl
        LB_01.ListIndex = -1
        Call UserForm_Initialize
End Sub
Private Sub cb04_Click()
Unload Me
End Sub
Private Sub cbx07_Change()
If cbx07.Value = "" Then tbx04.Value = ""
If cbx07.Value = "High" Then tbx04.Value = "H"
If cbx07.Value = "Medium" Then tbx04.Value = "M"
If cbx07.Value = "Low" Then tbx04.Value = "L"
End Sub


Private Sub Frame1_Click()


End Sub


Private Sub Frame2_Click()


End Sub


Private Sub Frame3_Click()


End Sub
Private Sub cb02_PRINT_Click()
Application.ScreenUpdating = False
If LB_01.ListIndex = -1 Then
        MsgBox "First choose a item in the list!", vbCritical, "Attention!"
        LB_01.SetFocus
        Exit Sub
    End If
    With Sheets("Print Project Data")
        Worksheets("Print Project Data").Visible = True
        .OLEObjects("textbox1").Object.Text = tbx01
        .OLEObjects("textbox2").Object.Text = cbx06
        .OLEObjects("textbox3").Object.Text = tbx12
        .OLEObjects("textbox4").Object.Text = cbx08
        .OLEObjects("textbox5").Object.Text = cbx07
        .OLEObjects("textbox6").Object.Text = tbx04
        .OLEObjects("textbox7").Object.Text = cbx02
        .OLEObjects("textbox8").Object.Text = tbx21
        .OLEObjects("textbox9").Object.Text = tbx22
        .OLEObjects("textbox10").Object.Text = tbx03
        .OLEObjects("textbox11").Object.Text = cbx13
        .OLEObjects("textbox12").Object.Text = cbx04
        .OLEObjects("textbox13").Object.Text = cbx05
        .OLEObjects("textbox14").Object.Text = tbx05
        .OLEObjects("textbox15").Object.Text = tbx07
        .OLEObjects("textbox16").Object.Text = cbx09
        .OLEObjects("textbox17").Object.Text = tbx06
        .OLEObjects("textbox18").Object.Text = tbx02
        .OLEObjects("textbox19").Object.Text = tbx13
        .OLEObjects("textbox20").Object.Text = tbx09
        .OLEObjects("textbox21").Object.Text = tbx14
        .OLEObjects("textbox22").Object.Text = tbx19
        .OLEObjects("textbox23").Object.Text = tbx08
        .OLEObjects("textbox24").Object.Text = tbx23
        .OLEObjects("textbox25").Object.Text = tbx15
        .OLEObjects("textbox26").Object.Text = tbx20
        .OLEObjects("textbox27").Object.Text = tbx24
        .OLEObjects("textbox28").Object.Text = tbx17
        .OLEObjects("textbox29").Object.Text = tbx18
        .OLEObjects("textbox30").Object.Text = cbx10
        .OLEObjects("textbox31").Object.Text = cbx11
        .OLEObjects("textbox32").Object.Text = cbx12
        .OLEObjects("textbox33").Object.Text = tbx10
        .OLEObjects("textbox34").Object.Text = cbx13
        .OLEObjects("textbox35").Object.Text = tbx11
        .PrintOut
        End With
        Application.ScreenUpdating = True
         Worksheets("Print Project Data").Visible = xlVeryHidden
         For Each ctrl In Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then ctrl.Value = ""
    Next ctrl
    For Each tbx In Sheets("Print Project Data").OLEObjects
        If TypeName(tbx.Object) = "TextBox" Then
            tbx.Object.Text = ""
        End If
    Next
    Call UserForm_Initialize
End Sub


Private Sub LB_01_Click()
'List box column order
        tbx01.Value = LB_01.Column(0)
        cbx06.Value = LB_01.Column(1)
        tbx12.Value = LB_01.Column(2)
        cbx08.Value = LB_01.Column(3)
        cbx07.Value = LB_01.Column(4)
        tbx04.Text = LB_01.Column(5)
        cbx02.Value = LB_01.Column(6)
        tbx21.Value = LB_01.Column(7)
        tbx22.Value = LB_01.Column(8)
        tbx03.Value = LB_01.Column(9)
        cbx13.Value = LB_01.Column(10)
        cbx04.Value = LB_01.Column(11)
        cbx05.Value = LB_01.Column(12)
        tbx05.Value = LB_01.Column(13)
        tbx07.Value = LB_01.Column(14)
        cbx09.Value = LB_01.Column(15)
        tbx06.Value = LB_01.Column(16)
        tbx02.Value = LB_01.Column(17)
        tbx13.Value = LB_01.Column(18)
        tbx09.Value = LB_01.Column(19)
        tbx14.Value = LB_01.Column(20)
        tbx19.Value = LB_01.Column(21)
        tbx08.Value = LB_01.Column(22)
        tbx23.Value = LB_01.Column(23)
        tbx15.Value = LB_01.Column(24)
        tbx24.Value = LB_01.Column(25)
        tbx16.Value = LB_01.Column(26)
        tbx17.Value = LB_01.Column(27)
        tbx18.Value = LB_01.Column(28)
        cbx10.Value = LB_01.Column(29)
        cbx11.Value = LB_01.Column(30)
        cbx12.Value = LB_01.Column(31)
        tbx10.Value = LB_01.Column(32)
        cbx13.Value = LB_01.Column(33)
        tbx11.Value = LB_01.Column(34)
    End Sub
Private Sub UserForm_Initialize()
Set collist = New Collection
With Worksheets("DROP DOWN LISTS")
    For i = 2 To 329
    On Error Resume Next
        collist.Add .Cells(i, 2).Value, CStr(.Cells(i, 2))
    Next i
        For j = 1 To collist.Count
        Next j
End With
cbx02.List = [Category].Value
cbx04.List = [savingstype].Value
cbx05.List = [onetimesavings].Value
cbx06.List = [wave].Value
cbx07.List = [confidencelevel].Value
cbx08.List = [projectstatus].Value
cbx09.List = [savingsrange].Value
cbx10.List = [pltrackingreq].Value
cbx11.List = [trackerinplace].Value
cbx12.List = [spotcheckreq].Value
tbx09.Value = Format(Date, "dd-mmm-yy")
cbx13.List = [initiativetype].Value
cbx14.List = [savingsflow].Value
tbx13.Value = Format(Date, "dd-mmm-yy")
tbx14.Value = Format(Date, "dd-mmm-yy")
tbx15.Value = Format(Date, "dd-mmm-yy")
tbx20.Value = Format(Date, "dd-mmm-yy")
tbx23.Value = Format(Date, "dd-mmm-yy")
tbx24.Value = Format(Date, "dd-mmm-yy")
tbx25.Value = Format(Date, "dd-mmm-yy")
tbx26.Value = Format(Date, "dd-mmm-yy")
With LB_01
    .List = Sheets("PGSSavingsTimline(Projections)").ListObjects("PGSSTP_tbl").DataBodyRange.Value
    .ColumnCount = [PGSSTP_tbl].CurrentRegion.Columns.Count
End With
End Sub

 

[daverunt]

In the previous post wasn't that fixed?

"Yes it creates and new line in the table, and yes it copies all of the traits from the line above (formulae and conditional formatting), but none of the data from the user-form is posted".

- Hence my suggestions it may not be referencing the form data correctly.

In this post it is back to "overwriting the data in the next row" and not creating a new row?

So I doubt I can help.

 

[Robert_Conklin]

I am not sure how to correct this. In short, I need the code I posted above to create a line in each table rather than overwrite the next line. I appreciate your time.

 

[NoSparks]

In the code of post 11, remove the TableRowInsert sub, it's pointless like that.

In this part of cb01_Click

With Sheets("PGS Score Card")
    iRow = .ListObjects("PGSSC_tbl").Range.Columns(2).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    .Cells(iRow, 3).Value = tbx01.Value
    .Cells(iRow, 5).Value = tbx21.Value
    .Cells(iRow, 6).Value = tbx02.Value
    .Cells(iRow, 7).Value = tbx18.Value
End With

the 3, 5, 6 and 7 refer to the sheet columns, is the first column of your table in column A ?
iRow will be the sheet row after the last data in the table second column whether it's at the bottom of the table or not,
I made the assumption that you would be writing a new record to the bottom of the tables, was that wrong ?

Have you tried, instead of typing tbx01.Value, typing Me. and selecting from the drop down ?

 

[Robert_Conklin]

Yes, 3, 5, 6, 7 refer to the sheet columns. The table starts in column B (why I do not know, I did not create the workbook). I have removed the table insert sub. You were correct in assuming that I would be creating the new line at the bottom of the table.

I am unfamiliar with the

instead of typing tbx01.Value, typing Me. and selecting from the drop down ?

 

[NoSparks]

You're best to either work with the sheet or work with the table, mixing the two is confusing.

If you go back to the code suggested in post 4, you'll be working with the table only, no matter where it's located on the sheet.
The column numbers in that post were what your previous code indicated which are wrong as apparrently they are sheet columns not table columns.

Here's another site that will help understand tables and its parts.

Re: the Me.
In the form code window, instead of typing
oNewRow.Range.Cells(1, 3).Value = tbx01.Value
I will type
oNewRow.Range.Cells(1, 3).Value = me.
and VBA intellisense displays a drop down that lists everything on the form.
this old post may help with that.

 

[Robert_Conklin]

Thanks Sparks, I will look them over and see what I can come up with.

 

[Robert_Conklin]

I went back to post 4 and changed my code. I also change the (PGS Scorcard) to read as an array like the two tables below it. I am getting a "Compile error: Variable not set" message when I try to run the code. It points to the variable in RED. I have the variable set, but I still get the message.

Option ExplicitDim iRow As Long, i As Long, j As Long
Dim ctrl As Control
Dim collist As Collection
Dim tbx As OLEObject
Dim oNewRow As ListRow
Private Sub cb01_Click()
Dim oNewRow As ListRow


With Sheets("PGS Score Card").ListObjects("PGSSC_tbl")
    Set oNewRow = [B][COLOR=#ff0000]ListRow[/COLOR][/B].Add(Alwaysinsert:=True)
        oNewRow.Range.Cells(1, 2).Resize(, 16).Value = Array(, , tbx01.Value, , tbx21.Value, tbx02.Value, tbx18.Value)
End With


With Sheets("PGSSavingsTimeline(Projections)").ListObjects("PGSSTP_tbl")
    Set oNewRow = ListRow.Add(Alwaysinsert:=True)
        oNewRow.Range.Cells(1, 2).Resize(, 22).Value = Array(cbx13.Value, tbx01.Value, cbx02.Value, tbx21.Value, tbx22.Value, tbx03.Value, cbx04.Value, cbx05.Value, _
        cbx06.Value, cbx07.Value, tbx04.Value, cbx08.Value, tbx26.Value, tbx05.Value, tbx06.Value, tbx07.Value, cbx09.Value, tbx09.Value, tbx08.Value, _
        tbx27.Value, tbx23.Value, tbx24.Value)
End With


With Sheets("PG&S Savings Timeline (Roll-up)").ListObjects("PGSSTRu_tbl")
    Set oNewRow = .ListRow.Add(Alwaysinsert:=True)
        oNewRow.Range.Cells(1, 2).Resize(, 35).Value = Array(, tbx01.Value, , , , , , cbx10.Value, cbx12.Value, tbx10.Value, cbx14.Value, tbx11.Value, _
        cbx11.Value, tbx12.Value, , , , , , , , , , , , tbx25.Value, tbx19.Value, , tbx15.Value, , , , tbx20.Value, tbx17.Value, tbx14.Value)
End With
  For Each ctrl In Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then ctrl.Value = ""
Next ctrl
    LB_01.ListIndex = -1
    Call UserForm_Initialize
End Sub

 

[Robert_Conklin]

I figured out my problem. I was missing a period (.) before ListRow. I also had a variable defined that I did not need anymore (iRow As Long). That corrected the error message and added a new row to my table. I also figured out the spacers for the table columns, so all data is posting to their correct corresponding cells.