Robert_Conklin
Board Regular
- Joined
- Jun 19, 2017
- Messages
- 173
- Office Version
- 365
- Platform
- Windows
- MacOS
My workbook uses userforms to create a new row in a three different table on three different worksheet in the same workbook. I have a listbox (LB_01) on the userform, but am not sure how to get the new data submitted from the userform to post in the listbox since I am using one userform to write to three different tables. Below is my code, and thank you for any suggestions:
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 ws As Worksheet
Private Sub cb01_Click()
Dim oNewRow As ListRow
With Sheets("PGS Score Card").ListObjects("PGSSC_tbl")
Set oNewRow = .ListRows.Add(Alwaysinsert:=True)
With oNewRow.Range
.Cells(1, 2) = Me.tbx01.Value
.Cells(1, 4) = Me.tbx21.Value
.Cells(1, 5) = Me.tbx02.Value
.Cells(1, 6) = Me.tbx18.Value
End With
With Sheets("PGSSavingsTimeline(Projections)").ListObjects("PGSSTP_tbl")
Set oNewRow = .ListRows.Add(Alwaysinsert:=True)
With oNewRow.Range
.Cells(1, 1) = Me.cbx13.Value
.Cells(1, 2) = Me.tbx01.Value
.Cells(1, 3) = Me.cbx02.Value
.Cells(1, 4) = Me.tbx21.Value
.Cells(1, 5) = Me.tbx22.Value
.Cells(1, 6) = Me.tbx03.Value
.Cells(1, 7) = Me.cbx04.Value
.Cells(1, 8) = Me.cbx05.Value
.Cells(1, 9) = Me.cbx06.Value
.Cells(1, 10) = Me.cbx07.Value
.Cells(1, 11) = Me.tbx04.Value
.Cells(1, 12) = Me.cbx08.Value
.Cells(1, 13) = Me.tbx26.Value
.Cells(1, 14) = Me.tbx05.Value
.Cells(1, 15) = Me.tbx06.Value
.Cells(1, 16) = Me.tbx07.Value
.Cells(1, 17) = Me.cbx09.Value
.Cells(1, 18) = Me.tbx09.Value
.Cells(1, 19) = Me.tbx08.Value
.Cells(1, 20) = Me.tbx27.Value
.Cells(1, 21) = Me.tbx23.Value
.Cells(1, 22) = Me.tbx24.Value
If MsgBox("Correct Entry?", vbYesNo + vbQuestion, "Check the Data!") = vbNo Then Exit Sub
MsgBox "The new entry has been saved", vbInformation, "done"
End With
With Sheets("PG&S Savings Timeline (Roll-up)").ListObjects("PGSSTRu_tbl")
Set oNewRow = .ListRows.Add(Alwaysinsert:=True)
With oNewRow.Range
.Cells(1, 2) = Me.tbx01.Value
.Cells(1, 8) = Me.cbx10.Value
.Cells(1, 9) = Me.cbx12.Value
.Cells(1, 10) = Me.tbx10.Value
.Cells(1, 11) = Me.cbx14.Value
.Cells(1, 12) = Me.tbx11.Value
.Cells(1, 13) = Me.cbx11.Value
.Cells(1, 14) = Me.tbx12.Value
.Cells(1, 26) = Me.tbx25.Value
.Cells(1, 27) = Me.tbx19.Value
.Cells(1, 29) = Me.tbx15.Value
.Cells(1, 33) = Me.tbx20.Value
.Cells(1, 34) = Me.tbx17.Value
.Cells(1, 35) = Me.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 With
End With
End With
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
Last edited: