Submitted Data from a Userform is not Posting to a ListBox

R

Robert_Conklin

Board Regular
Joined
Jun 19, 2017
Messages
173
Office Version
  1. 365
Platform
  1. Windows
  2. 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:
Sort by date Sort by votes
That was actually my first suspicion and why I questioned the sheet having the same name as the list box.

Try changing to either With Me.LB_01
or eliminate the With - End With altogether and use two lines
Me.LB_01.List =
Me.LB_01.ColumnCount =
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
I modified the code as above with and without the With/End With statement and I still receive the same run-time error. It highlights the "Me.LB_01.List =" line. When I hover my cursor over that section a comment pops up that reads "Me.LB_01.List = Null" Does that mean that I do not have any data on my ListBox Data sheet?
 
Upvote 0
I didn't complete those two lines, you always need something on both sides of an equal sign.
If I've followed right, they'll be
Me.LB_01.List = Sheets("ListBox Data").ListObjects("PGSLB_01_tbl").DataBodyRange.Value
and
Me.LB_01.ColumnCount = [PGSLB_01_tbl].CurrentRegion.Columns.Count

Personally, I wouldn't bother with the second line.
You've just created the table and know exactly how many columns there are so just enter that as the list box column count property.
I'm sure you'll be in the properties to set the column widths as well.
 
Upvote 0
I assumed that is what you meant, just change the beginning of the lines. The two lines of code are exactly as above. Yes, there are 37 columns on the ListBox Data sheet. The only column count properties that I have written are in the range coding I had in the last thread "https://www.mrexcel.com/forum/excel-questions/1055905-new-row-added-table-does-not-bring-down-formulae-conditional-formatting-line-above.html:" post #1 . There I specified resize (column count). I am not sure how to apply them in this instance. Is it the same?
 
Upvote 0
The second line above sets the ColumnCount property of the listbox.
You know it's 37 because that's the number of columns you made the table.
So just do things the easy way without code...
In design mode, right click on the listbox, select properties, the fifth or sixth property down is ColumnCount, default is 1, change that to 37, that's it, done.
You'll eventually want to adjust the width of the individual columns for neatness in the listbox, that's a couple properties down as ColumnWidths.
 
Upvote 0
I completely forgot about the properties windows. Thank you. I made the changes, ran the code, but still receive the Run-time error 91 - Object variable or With block variable not set. It still points to the "Me.LB_01.List = Sheets("ListBox Data").ListObjects("PGSLB_01_tbl").DataBodyRange.Value" line of code. I have checked all of the With/End With statements throughout the form and they are all accounted for. Do I need to move that line of code to another location in the form? Is there a setting that I can change that will give me more information about the error?

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


Application.ScreenUpdating = False


'first table
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
End With


'second table
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
    End With
End With


'third table
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
End With


'fourth table
With Sheets("LB_01").ListObjects("PGSLB_01_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
        .Cells(1, 23) = Me.tbx02.Value
        .Cells(1, 24) = Me.tbx18.Value
        .Cells(1, 25) = Me.cbx10.Value
        .Cells(1, 26) = Me.cbx11.Value
        .Cells(1, 27) = Me.cbx12.Value
        .Cells(1, 28) = Me.tbx10.Value
        .Cells(1, 29) = Me.cbx14.Value
        .Cells(1, 30) = Me.tbx12.Value
        .Cells(1, 31) = Me.tbx19.Value
        .Cells(1, 32) = Me.tbx25.Value
        .Cells(1, 33) = Me.tbx14.Value
        .Cells(1, 34) = Me.tbx15.Value
        .Cells(1, 35) = Me.tbx20.Value
        .Cells(1, 36) = Me.tbx17.Value
        .Cells(1, 37) = Me.tbx11.Value
    End With
End With


'messages
If MsgBox("Correct Entry?", vbYesNo + vbQuestion, "Check the Data!") = vbNo Then Exit Sub
MsgBox "The new entry has been saved", vbInformation, "done"
     
'clear control values
For Each ctrl In Controls
    If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then ctrl.Value = ""
Next ctrl


'select nothing
LB_01.ListIndex = -1


're-initialize
Call UserForm_Initialize


'turn screen update back on
Application.ScreenUpdating = True


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()
'Close Userform
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
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 Label29_Click()


End Sub


Private Sub LB_01_Click()
'List box column order
        cbx13.Value = LB_01.Column(0)
        tbx01.Value = LB_01.Column(1)
        cbx02.Value = LB_01.Column(2)
        tbx21.Value = LB_01.Column(3)
        tbx22.Value = LB_01.Column(4)
        tbx03.Text = LB_01.Column(5)
        cbx04.Value = LB_01.Column(6)
        cbx05.Value = LB_01.Column(7)
        cbx06.Value = LB_01.Column(8)
        cbx07.Value = LB_01.Column(9)
        tbx04.Value = LB_01.Column(10)
        cbx08.Value = LB_01.Column(11)
        tbx26.Value = LB_01.Column(12)
        tbx05.Value = LB_01.Column(13)
        tbx06.Value = LB_01.Column(14)
        tbx07.Value = LB_01.Column(15)
        cbx09.Value = LB_01.Column(16)
        tbx09.Value = LB_01.Column(17)
        tbx08.Value = LB_01.Column(18)
        tbx27.Value = LB_01.Column(19)
        tbx23.Value = LB_01.Column(20)
        tbx24.Value = LB_01.Column(21)
        tbx02.Value = LB_01.Column(22)
        tbx18.Value = LB_01.Column(23)
        cbx10.Value = LB_01.Column(24)
        cbx11.Value = LB_01.Column(25)
        cbx12.Value = LB_01.Column(26)
        tbx10.Value = LB_01.Column(27)
        cbx14.Value = LB_01.Column(28)
        tbx12.Value = LB_01.Column(29)
        tbx19.Value = LB_01.Column(30)
        tbx25.Value = LB_01.Column(31)
        tbx14.Value = LB_01.Column(32)
        tbx15.Value = LB_01.Column(33)
        tbx20.Value = LB_01.Column(34)
        tbx17.Value = LB_01.Column(35)
        tbx11.Value = LB_01.Column(36)
        
    End Sub


Private Sub tbx21_Change()


End Sub


Private Sub UserForm_Initialize()
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
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")


Me.LB_01.List = Sheets("ListBox Data").ListObjects("PGSLB_01_tbl").DataBodyRange.Value


End Sub
 
Upvote 0
Do you have any data in the new table that can be loaded into the list box?
If not manually add a first row of bogus data and try it.
 
Upvote 0
Too late to edit previous post...
or could use
Code: 
With Sheets("ListBox Data").ListObjects("PGSLB_01_tbl")
    If .ListRows.Count > 0 Then Me.LB_01.List = .DataBodyRange.Value
End With
 
Upvote 0
BEAUTIFUL!! It works perfectly!! Once that was complete, I was able to get my Print Sub working. Thanks again Sparks for ALL your help and guidance!!
 
Upvote 0
This skips first and third table if project number exists, not sure what you have in mind for fourth and fifth tables.
You can move them up into the If part to skip writing to them.
Code: 
Private Sub cb01_Click()
    Dim oNewRow As ListRow
    Dim ExistingProj As Range

Application.ScreenUpdating = False

'determine if project number (tbx01) already exists in "PGSSTP_tbl" col 2
On Error Resume Next    'suppress error message if doesn't exist
Set ExistingProj = Sheets("PGSSavingsTimeline(Projections)").ListObjects("PGSSTP_tbl").ListColumns(2).DataBodyRange.Find _
    (What:=Me.tbx01.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
On Error GoTo 0         're-enable error messages

If ExistingProj Is Nothing Then     'new project so write to these tables
    'first table
    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
    End With
    
    'third table
    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
    End With
End If

'always write to these tables

'second table
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
    End With
End With


'fourth table
With Sheets("Weekly Variance").ListObjects("PGSWV_tbl")
    Set oNewRow = .ListRows.Add(Alwaysinsert:=True)
    With oNewRow.Range
        .Cells(1, 1) = Me.cbx13.Value
        .Cells(1, 1) = Me.tbx01.Value
        .Cells(1, 2) = Me.cbx02.Value
        .Cells(1, 5) = Me.tbx21.Value
        .Cells(1, 6) = Me.tbx22.Value
    End With
End With

'fifth table
With Sheets("ListBox Data").ListObjects("PGSLB_01_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
        .Cells(1, 23) = Me.tbx02.Value
        .Cells(1, 24) = Me.tbx18.Value
        .Cells(1, 25) = Me.cbx10.Value
        .Cells(1, 26) = Me.cbx11.Value
        .Cells(1, 27) = Me.cbx12.Value
        .Cells(1, 28) = Me.tbx10.Value
        .Cells(1, 29) = Me.cbx14.Value
        .Cells(1, 30) = Me.tbx12.Value
        .Cells(1, 31) = Me.tbx19.Value
        .Cells(1, 32) = Me.tbx25.Value
        .Cells(1, 33) = Me.tbx14.Value
        .Cells(1, 34) = Me.tbx15.Value
        .Cells(1, 35) = Me.tbx20.Value
        .Cells(1, 36) = Me.tbx17.Value
        .Cells(1, 37) = Me.tbx11.Value
    End With
End With

'messages
If MsgBox("Correct Entry?", vbYesNo + vbQuestion, "Check the Data!") = vbNo Then Exit Sub
MsgBox "The new entry has been saved", vbInformation, "done"
     
'clear control values
For Each ctrl In Controls
    If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then ctrl.Value = ""
Next ctrl

'select nothing
LB_01.ListIndex = -1

're-initialize
Call UserForm_Initialize

'turn screen update back on
Application.ScreenUpdating = True

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
How restrict copy data from form to sheet based on five days within current month
Replies
3
Views
79
Ali M
A
S
  • Question Question
Excel VBA Help
Replies
1
Views
71
DanteAmor
DanteAmor
N
  • Solved
Frame scrollbar resets when clicking on/in frame
Replies
5
Views
341
Micron
M
P
  • Solved
Userform textbox populated with listbox data how to edit
Replies
6
Views
1K
pure vito
P
C
  • Question Question
Excel Userform Listbox Search issues
Replies
3
Views
732
Akuini
Akuini

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top