New row added to table does not bring down formulae and conditional formatting from the line above.

[Robert_Conklin]

I recently got my VBA code working to create a new row in my tables without overwriting data in the line below. The issue I am having is there are some cells in my workbook that have formulae and some have conditional formatting that the new table row needs to inherit from the line above when it is created. These cells that contain these formulae and cond. formatting pull data from other sheets in the workbook. Below is my code, any insight would be greatly appreciated.

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


With Sheets("PGS Score Card").ListObjects("PGSSC_tbl")
     Set oNewRow = .ListRows.Add(Alwaysinsert:=True)
         oNewRow.Range.Cells(1, 2).Resize(, 17).Value = Array(tbx01.Value, , tbx21.Value, tbx02.Value, tbx18.Value)
End With


With Sheets("PGSSavingsTimeline(Projections)").ListObjects("PGSSTP_tbl")
     Set oNewRow = .ListRows.Add(Alwaysinsert:=True)
         oNewRow.Range.Cells(1, 1).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 = .ListRows.Add(Alwaysinsert:=True)
         oNewRow.Range.Cells(1, 2).Resize(, 22).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

 

[NoSparks]

I'm not sure what you're asking about the code in red in post 7.
That sub sets up the start condition for the user form, nothing to do with the sheets.
I see the collection collist being made, but not used for anything.
With LB_01, think I'd use listcolumns.count rather than currentregion.

I don't know why your conditional formatting wouldn't be extended when adding a row to the table, that's something that is supposed to happen and an advantage for using tables.
Try removing and reapplying the conditional formatting for that entire column databodyrange and see if it will extend after that.

If that doesn't work you're probably best to start a new thread specific to the problem, it's not something I've ever dealt with.

 

[Robert_Conklin]

I was brought up the code in read because there I was defining the formatting for each of the fields in my user-form. I have no idea what the Collist. does or why it is there, I did not write that part of the code. The LB_01 is another problem I am having with the code, The data submitted from the user-form does not place a line in LB_01, but I was going to create another thread to deal with that.

I tried your suggestion about reformatting all of the cells in the columns affected by the conditional formatting issue, but that did not work. I am unsure what is going on there. I will start another thread as suggested.

Thanks a million for your help on this, I am 100X's closer to the end result than I was before!!

 

[NoSparks]

After re-reading this thread (and your new one), I'm thinking 'conditional formatting' is an error in terminology.
Do you have Conditional Formatting Rules applied to the first data cell in these particular table columns?
(Select one of those first cells, from the Home ribbon, click Conditional Formatting and select Manage Rules.)

If not, try applying cell formatting to the cells at the same time the text box values are written to the tables.
Something along the lines of

With Sheets("PGS Score Card").ListObjects("PGSSC_tbl")
     Set oNewRow = .ListRows.Add(Alwaysinsert:=True)
         With oNewRow.Range
            .Cells(1, 2) = Me.tbx01
            .Cells(1, 4) = Me.tbx21
            .Cells(1, 5) = Me.tbx02
                .Cells(1, 5).NumberFormat = "_($* #,##0_);_($* (#,##0);_(S* ""_""_);_(@_)"
            .Cells(1, 6) = Me.tbx18
                .Cells(1, 6).NumberFormat = "_($* #,##0, ""K"";_($* #,##0,""K"");_($* ""_"");_(@_)"
        End With
End With

these formats were from post 8
note that the format has to be in double quotes and double quotes within the format need to be doubled-up.

 

[Robert_Conklin]

You are correct, I was under the impression that a custom number format WAS Conditional Formatting. Obviously I was mistaken. On the table in the code referenced above there are 17 Conditional Formatting rules. I tried adding the appropriate custom formatting as shown in the code above, but my same issue remains. Here is the modified code:

With Sheets("PGS Score Card").ListObjects("PGSSC_tbl")     Set oNewRow = .ListRows.Add(Alwaysinsert:=True)
         With oNewRow.Range
            .Cells(1, 2) = Me.tbx01
            .Cells(1, 4) = Me.tbx21
            .Cells(1, 5) = Me.tbx02
               .Cells(1, 5).NumberFormat = "$0.0,,""MM"""
            .Cells(1, 6) = Me.tbx18
               .Cells(1, 6).NumberFormat = "$0.0,,""MM"""
End With

 

[Robert_Conklin]

Also, after learning that they are not the same, it is not the conditional formatting that is the issue, it is the Custom Number Formatting that is not copying from the line above.

 

[NoSparks]

Without seeing what the situation actually is, everything is just a guess.

Maybe the number formatting should happen ahead of writing a value to the cell.
I'm pretty sure that upon execution of the code line applying the custom number format to the cell, that's what the cell number format will be.

All I can suggest is using a breakpoint and stepping through the code one line at a time with the F8 key,
checking the table after each line to figure out what/when/why things are happening.

Other than that, afraid all I can do is wish you luck.

 

[Robert_Conklin]

I can send you what I am working on if you want to take a peek.

 

[Robert_Conklin]

The solution to this problem was I was missing the .Value behind my field names. The corrected code is below.

Private Sub cb01_Click()Dim oNewRow As ListRow[/INDENT]
[/COLOR][/COLOR]

[COLOR=#333333]With Sheets("PGS Score Card").ListObjects("PGSSC_tbl")[/COLOR]
[COLOR=#333333]Set oNewRow = .ListRows.Add(Alwaysinsert:=True)[/COLOR]

[COLOR=#333333]With oNewRow.Range[/COLOR]
[COLOR=#333333].Cells(1, 2) = Me.tbx01.Value[/COLOR]
[COLOR=#333333].Cells(1, 4) = Me.tbx21.Value[/COLOR]
[COLOR=#333333].Cells(1, 5) = Me.tbx02.Value[/COLOR]
[COLOR=#333333].Cells(1, 6) = Me.tbx18.Value[/COLOR]

[COLOR=#333333]End With[/COLOR]


[COLOR=#333333]With Sheets("PGSSavingsTimeline(Projections)").ListObjects("PGSSTP_tbl")[/COLOR]
[COLOR=#333333]Set oNewRow = .ListRows.Add(Alwaysinsert:=True)[/COLOR]

[COLOR=#333333]With oNewRow.Range[/COLOR]
[COLOR=#333333].Cells(1, 1) = Me.cbx13.Value[/COLOR]
[COLOR=#333333].Cells(1, 2) = Me.tbx01.Value[/COLOR]
[COLOR=#333333].Cells(1, 3) = Me.cbx02.Value[/COLOR]
[COLOR=#333333].Cells(1, 4) = Me.tbx21.Value[/COLOR]
[COLOR=#333333].Cells(1, 5) = Me.tbx22.Value[/COLOR]
[COLOR=#333333].Cells(1, 6) = Me.tbx03.Value[/COLOR]
[COLOR=#333333].Cells(1, 7) = Me.cbx04.Value[/COLOR]
[COLOR=#333333].Cells(1, 8) = Me.cbx05.Value[/COLOR]
[COLOR=#333333].Cells(1, 9) = Me.cbx06.Value[/COLOR]
[COLOR=#333333].Cells(1, 10) = Me.cbx07.Value[/COLOR]
[COLOR=#333333].Cells(1, 11) = Me.tbx04.Value[/COLOR]
[COLOR=#333333].Cells(1, 12) = Me.cbx08.Value[/COLOR]
[COLOR=#333333].Cells(1, 13) = Me.tbx26.Value[/COLOR]
[COLOR=#333333].Cells(1, 14) = Me.tbx05.Value[/COLOR]
[COLOR=#333333].Cells(1, 15) = Me.tbx06.Value[/COLOR]
[COLOR=#333333].Cells(1, 16) = Me.tbx07.Value[/COLOR]
[COLOR=#333333].Cells(1, 17) = Me.cbx09.Value[/COLOR]
[COLOR=#333333].Cells(1, 18) = Me.tbx09.Value[/COLOR]
[COLOR=#333333].Cells(1, 19) = Me.tbx08.Value[/COLOR]
[COLOR=#333333].Cells(1, 20) = Me.tbx27.Value[/COLOR]
[COLOR=#333333].Cells(1, 21) = Me.tbx23.Value[/COLOR]
[COLOR=#333333].Cells(1, 22) = Me.tbx24.Value[/COLOR]
[COLOR=#333333]End With[/COLOR]


[COLOR=#333333]With Sheets("PG&S Savings Timeline (Roll-up)").ListObjects("PGSSTRu_tbl")[/COLOR]
[COLOR=#333333]Set oNewRow = .ListRows.Add(Alwaysinsert:=True)[/COLOR]
[COLOR=#333333]With oNewRow.Range[/COLOR]
[COLOR=#333333].Cells(1, 2) = Me.tbx01.Value[/COLOR]
[COLOR=#333333].Cells(1, 8) = Me.cbx10.Value[/COLOR]
[COLOR=#333333].Cells(1, 9) = Me.cbx12.Value[/COLOR]
[COLOR=#333333].Cells(1, 10) = Me.tbx10.Value[/COLOR]
[COLOR=#333333].Cells(1, 11) = Me.cbx14.Value[/COLOR]
[COLOR=#333333].Cells(1, 12) = Me.tbx11.Value[/COLOR]
[COLOR=#333333].Cells(1, 13) = Me.cbx11.Value[/COLOR]
[COLOR=#333333].Cells(1, 14) = Me.tbx12.Value[/COLOR]
[COLOR=#333333].Cells(1, 26) = Me.tbx25.Value[/COLOR]
[COLOR=#333333].Cells(1, 27) = Me.tbx19.Value[/COLOR]
[COLOR=#333333].Cells(1, 29) = Me.tbx15.Value[/COLOR]
[COLOR=#333333].Cells(1, 33) = Me.tbx20.Value[/COLOR]
[COLOR=#333333].Cells(1, 34) = Me.tbx17.Value[/COLOR]
[COLOR=#333333].Cells(1, 35) = Me.tbx14.Value[/COLOR]
[COLOR=#333333]End With[/COLOR]


[COLOR=#333333]For Each ctrl In Controls[/COLOR]
[COLOR=#333333]If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then ctrl.Value = ""[/COLOR]
[COLOR=#333333]Next ctrl[/COLOR]
[COLOR=#333333]LB_01.ListIndex = -1[/COLOR]
[COLOR=#333333]Call UserForm_Initialize[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#574123][COLOR=#333333][INDENT]End Sub

Edit Post Reply Reply With Quote​