Robert_Conklin
Board Regular
- Joined
- Jun 19, 2017
- Messages
- 173
- Office Version
- 365
- Platform
- Windows
- MacOS
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.
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 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
