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.

 

[NoSparks]

check out the part of this site http://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp?AllComments=True regarding Inserting rows and columns.

 

[Robert_Conklin]

I incorporated the Sub to insert a new line, and the below is what happened:

The issue with the entry overwriting the first (top) line of the table was resolved, it now posts to the bottom of the table.
The problem still exists in regards to the entry not creating a new line in the table. It just overwrites the next line. This is now the case for ALL three tables. Below is my new code.

Private Sub TableRowInsert()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, 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

 

[NoSparks]

Completely untested but you'll get the idea.
May not actually need the Alwaysinsert:=True

Private Sub cb01_Click()
    Dim oNewRow As ListRow

With Sheets("PGS Score Card").ListObjects("PGSSC_tbl")  'the sheet and table
    Set oNewRow = .ListRows.Add Alwaysinsert:=True     'add new row to table
        oNewRow.Range.Cells(1, 3).Value = tbx01.Value   '3 is third column in the new row
        oNewRow.Range.Cells(1, 5).Value = tbx21.Value
        oNewRow.Range.Cells(1, 6).Value = tbx02.Value
        oNewRow.Range.Cells(1, 7).Value = tbx18.Value
End With

With Sheets("PGSSavingsTimeline(Projections)").ListObjects("PGSSTP_tbl")
    Set oNewRow = .ListRows.Add Alwaysinsert:=True
    oNewRow.Range.Cells(1, 2).Resize(, 22).Value = that array stuff
    '
End With

With Sheets("PG&S Savings Timeline (Roll-up)").ListObjects("PGSSTRu_tbl")
    Set oNewRow = .ListRows.Add Alwaysinsert:=True
    onewrow.Range.Cells(1,2).resize(,35).value =  that array stuff
    '
End With

'rest of code

End Sub

Hope that helps

 

[Robert_Conklin]

The "Set oNewRow = .ListRows.Add Alwaysinsert:=True" piece of code gave an error right off the bat. I incorporated the rest of the code and the same error message popped up. I removed the aforementioned code and ran it again, but it error-ed out again.

 

[NoSparks]

... gave an error ... same error message popped up ... it error-ed out again.

Sorry buds, won't be guessing what Excel/VBA said the errors were, nor the exact code you tried.

If you were to share a sample workbook, any suggestion would be verified prior to posting,
other than that afraid I can't help you.

 

[Robert_Conklin]

My apologies, I was only keeping you informed of what was going on. I was taking your idea and trying to make it work. I do not want you to do all of the work. So far, this is what I have. The "Set NewRow = .ListRows.Add AlwaysInsert:=True" portion of the code has to be in parentheses before it will work. Now there are no error messages.

Private Sub cb01_Click()Dim NewRow As ListRow


With Sheets("PGS Score Card").ListObjects("PGSSC_tbl")
    Set NewRow = .ListRows.Add(AlwaysInsert:=True)
    NewRow.Range.Cells(iRow, 3).Value = tbx01.Value
    NewRow.Range.Cells(iRow, 5).Value = tbx21.Value
    NewRow.Range.Cells(iRow, 6).Value = tbx02.Value
    NewRow.Range.Cells(iRow, 7).Value = tbx18.Value
End With

The problem is that it is not posting the data from the user-form to its respective locations in the table. 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.

I would post the workbook, but I do not have permissions to do so.

 

[daverunt]

Does tbx01.Text work instead of Value?

 

[Robert_Conklin]

No it doesn't. The only data that transfers from the user-form to the worksheet is the "NEW Project number", but is posts in D100 instead of C101. I am attaching an image, but I am not entirely sure you will be able to see it.

 

[daverunt]

Can't see the picture.
The only other thing I can think of is that the references to the textboxes are not right. I don't see any reference to the UserForm in the posted code. Or the TextBox(X) either just tbx0(x)

Something along the lines of:

UserForm1.Controls("TextBox1").Text