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:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
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).
 
Upvote 0
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.
 
Upvote 0
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?
Click to expand...

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?
Click to expand...
Sorry, I kinda think so but don't really know how to deal with that.
 
Upvote 0
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
 
Upvote 0
Not now...The sheet name is now "ListBox Data". I still receive the same run-time error message.
 
Upvote 0
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
 
Upvote 0
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.
 
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
112
Ali M
A
S
  • Question Question
Excel VBA Help
Replies
1
Views
102
DanteAmor
DanteAmor
N
  • Solved
Frame scrollbar resets when clicking on/in frame
Replies
5
Views
366
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
755
Akuini
Akuini

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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