Submitted Data from a Userform is not Posting to a ListBox

Robert_Conklin

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

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
829
Office Version
2010
Platform
Windows
The list box is populated from only one of the tables, so after updating the tables, you need to re-apply the LB_01.List =
This is being done within the cb01_Click sub after writing to the tables by calling UserForm_Initialize.
So issues are actually within the UserForm_Initialize sub.

The collist collection part of the initialize sub isn't necessary.
collist has been declared as a global variable but isn't used anywhere in the module.
If you remove this part
Code:
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
The elimination of On Error Resume Next will allow Excel/vba to notify you of errors (such as spelling a sheet name wrong).
 

Robert_Conklin

Board Regular
Joined
Jun 19, 2017
Messages
120
I removed that portion of the code. When I run the code the userform comes up, and in the list box are ALL of the lines on the PGSSavingsTimeline(Projections) sheet. Not just what is in the table. Does that have something to do with my ListIndex = -1? In actuality, all I need are the fields that are on the userform. I am afraid that if I pull from the three tables, I am going to have a lot of duplicate columns. Do I need to create another sheet to consolidate all of the data from the userform and pull that data into LB_01? Also, the lines that are listed in my ListBox, I have to be able to click on the line (in the listbox) and the data repopulate into the userform for verification/change purposes. Any ideas for a solution to this.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
829
Office Version
2010
Platform
Windows
I removed that portion of the code. When I run the code the userform comes up, and in the list box are ALL of the lines on the PGSSavingsTimeline(Projections) sheet. Not just what is in the table. Does that have something to do with my ListIndex = -1?
ListIndex = -1 means no line of the list box is selected.

I'm not sure as to what you think that table consists of.
In the cb01_Click sub, you write to 22 columns of PGSSTP_tbl
In LB_01_Click you populate the form from 35 columns of PGSSTP_tbl

If you select the table name in the name box to the left of the formula bar it will highlight the table, which is what the line
.List = Sheets("PGSSavingsTimeline(Projections)").ListObjects("PGSSTP_tbl").DataBodyRange.Value
is loading into the listbox (all 90 columns)

Do I need to create another sheet to consolidate all of the data from the userform and pull that data into LB_01?
Sorry, I kinda think so but don't really know how to deal with that.
 

Robert_Conklin

Board Regular
Joined
Jun 19, 2017
Messages
120
Thanks, I was afraid of that. I will work on it and post the results.
 

Robert_Conklin

Board Regular
Joined
Jun 19, 2017
Messages
120
Yep, I got confused on my tables. I created another sheet in the workbook titled "LB_01", consolidated all of the data that I need in my listbox and created table "PGSLB_01_tbl". I wrote the code just like writing to the other tables. Lastly, I updated my
Code:
With LB_01    .List = Sheets("LB_01").ListObjects("PGSLB_01_tbl").DataBodyRange.Value
    .ColumnCount = [PGSLB_01_tbl].CurrentRegion.Columns.Count
to reflect the new table. I also updated my LB_01_Click sub to reflect the same column order as my LB_01 sheet. When I run the code, I immediately get an error message "Run-time error '91': Object variable or With block variable not set." Not sure what is causing it. I did not change anything else other than what I described. Updated code is below:

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
Dim oNewRow As ListRow
Private Sub cb01_Click()
Application.ScreenUpdating = False




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


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
            
     If MsgBox("Correct Entry?", vbYesNo + vbQuestion, "Check the Data!") = vbNo Then Exit Sub
     MsgBox "The new entry has been saved", vbInformation, "done"
     
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
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
        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 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")


With LB_01
    .List = Sheets("LB_01").ListObjects("PGSLB_01_tbl").DataBodyRange.Value
    .ColumnCount = [PGSLB_01_tbl].CurrentRegion.Columns.Count
End With
End Sub
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
829
Office Version
2010
Platform
Windows
The new sheet and the listbox have the same name ?
 

Robert_Conklin

Board Regular
Joined
Jun 19, 2017
Messages
120
Not now...The sheet name is now "ListBox Data". I still receive the same run-time error message.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
829
Office Version
2010
Platform
Windows
Keep your With - End With blocks more together, you had 8 With and 7 End With
try this way
Code:
Private Sub cb01_Click()

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
 

Robert_Conklin

Board Regular
Joined
Jun 19, 2017
Messages
120
You are right, I was missing one. I cleaned up that code, but still receive the same run-time error. It point to the With LB_01 portion of the UserForm_Initialize() sub. I have triple checked the spelling and everything is correct.
 

Forum statistics

Threads
1,081,702
Messages
5,360,743
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top