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
Thanks for looking at it so quickly!! It works perfectly! I added the fourth table to the mix and everything works perfectly!
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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,324
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