Problems with Adding a new line to a table

Robert_Conklin

Board Regular
Joined
Jun 19, 2017
Messages
126
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.

Code:
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,769
Office Version
2013
Platform
Windows
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

Board Regular
Joined
Jun 19, 2017
Messages
126
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

Well-known Member
Joined
Mar 15, 2013
Messages
874
Office Version
2010
Platform
Windows
In the code of post 11, remove the TableRowInsert sub, it's pointless like that.

In this part of cb01_Click
Code:
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

Board Regular
Joined
Jun 19, 2017
Messages
126
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

Well-known Member
Joined
Mar 15, 2013
Messages
874
Office Version
2010
Platform
Windows
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

Board Regular
Joined
Jun 19, 2017
Messages
126
Thanks Sparks, I will look them over and see what I can come up with.
 

Robert_Conklin

Board Regular
Joined
Jun 19, 2017
Messages
126
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.

Code:
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

Board Regular
Joined
Jun 19, 2017
Messages
126
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,931
Messages
5,411,330
Members
403,362
Latest member
DoubleJay

This Week's Hot Topics

Top