Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Combobox Worksheet Selection not populating Listbox

0

I got the following code to populate the combobox with the worksheet names but I can't seem to get the listbox to populate with the items on the worksheet selected.

Const Root As String = "PT-"

Private Sub Cb_Submit_Click()

Dim Picked As String

' loop through Listbox2

With Me.lstSELpart
    For x = 0 To .ListCount - 1
        ' get Item# and Parts name (e.g.) from List
        If .Selected(x) = True Then
           Picked = Picked & .List(x, 0) & " " & .List(x, 1) & vbCrLf
        End If
    Next x
End With

' tell user
MsgBox "You picked:" & vbCrLf & vbCrLf & Picked & vbCrLf & "from " & Root & cmbGunSel.Text
Unload Me


End Sub


Private Sub cmbGunSel_Change()
Dim Index As Long, Col As Long, Val As Variant

' do nothing if no model was picked
If Me.cmbGunSel.Value = "" Then Exit Sub

' clear previous list
Me.lstSELpart.Clear

'do nothing if an invalid name was typed in
On Error Resume Next
If ThisWorkbook.Worksheets(Root & Me.cmbGunSel.Value).Name <> Root & Me.lstSELpart.Value Then Exit Sub

' with chosen source
With ThisWorkbook.Worksheets(Root & Me.cmbGunSel.Value)

    ' populate  second list box with checked items only
    For Each Ct In .Shapes
        If InStr(Ct.Name, "Check Box") = 1 And Ct.OLEFormat.Object.Value = 1 Then
            ' populate columns of ListBox2
            Me.lstSELpart.AddItem .Range(Ct.TopLeftCell.Address).Offset(0, 1).Text
            Index = 1
            ' loop through columns of checked item
            For Col = 2 To 4
                Val = .Range(Ct.TopLeftCell.Address).Offset(0, Col).Value
                ' format the currency column
                If Col = 4 Then Val = Format(Val, "$#,##0.00")
                Me.lstSELpart.List(Me.lstSELpart.ListCount - 1, Index) = Val
                ' increase the column index
                Index = Index + 2
            Next Col
        End If
    Next Ct
    ' set up LB2 columns
    Me.lstSELpart.ColumnCount = 4
    Me.lstSELpart.ColumnWidths = "40;200;40;40"
    
End With

End Sub


Private Sub UserForm_Initialize()

Dim ws As Worksheet, Ct As Shape

' loop through sheets and set data sources
For Each ws In ThisWorkbook.Worksheets
    ' check sheet name starts with root
    If InStr(ws.Name, Root) = 1 Then
        ' add name (less Root) to ComboBox list
        Me.cmbGunSel.AddItem Split(ws.Name, Root)(1)
    End If

Next ws

End Sub

I was given this code and all I did was change the combobox and listbox name. 

Const Root As String = "PT-"

Private Sub Cb_Submit_Click()

Dim Picked As String

' loop through Listbox2

With Me.ListBox2
    For x = 0 To .ListCount - 1
        ' get Item# and Parts name (e.g.) from List
        If .Selected(x) = True Then
           Picked = Picked & .List(x, 0) & " " & .List(x, 1) & vbCrLf
        End If
    Next x
End With

' tell user
MsgBox "You picked:" & vbCrLf & vbCrLf & Picked & vbCrLf & "from " & Root & ComboBox1.Text
Unload Me


End Sub

Private Sub ComboBox1_Change()

Dim Index As Long, Col As Long, Val As Variant

' do nothing if no model was picked
If Me.ComboBox1.Value = "" Then Exit Sub

' clear previous list
Me.ListBox2.Clear

'do nothing if an invalid name was typed in
On Error Resume Next
If ThisWorkbook.Worksheets(Root & Me.ComboBox1.Value).Name <> Root & Me.ComboBox1.Value Then Exit Sub

' with chosen source
With ThisWorkbook.Worksheets(Root & Me.ComboBox1.Value)

    ' populate  second list box with checked items only
    For Each Ct In .Shapes
        If InStr(Ct.Name, "Check Box") = 1 And Ct.OLEFormat.Object.Value = 1 Then
            ' populate columns of ListBox2
            Me.ListBox2.AddItem .Range(Ct.TopLeftCell.Address).Offset(0, 1).Text
            Index = 1
            ' loop through columns of checked item
            For Col = 2 To 4
                Val = .Range(Ct.TopLeftCell.Address).Offset(0, Col).Value
                ' format the currency column
                If Col = 4 Then Val = Format(Val, "$#,##0.00")
                Me.ListBox2.List(Me.ListBox2.ListCount - 1, Index) = Val
                ' increase the column index
                Index = Index + 1
            Next Col
        End If
    Next Ct
    ' set up LB2 columns
    Me.ListBox2.ColumnCount = 4
    Me.ListBox2.ColumnWidths = "40;200;40;40"
    
End With

End Sub

Private Sub UserForm_Initialize()

Dim ws As Worksheet, Ct As Shape

' loop through sheets and set data sources
For Each ws In ThisWorkbook.Worksheets
    ' check sheet name starts with root
    If InStr(ws.Name, Root) = 1 Then
        ' add name (less Root) to ComboBox list
        Me.ComboBox1.AddItem Split(ws.Name, Root)(1)
    End If

Next ws

End Sub

So I'm wondering am I missing something because it's conflicting with a code in my workbook.

I added the 2 files I'm working with for now. The file that I have added has the userform "frmGunParts" that I have included red and white boxes with instructions of what I want to do in my next phases. It's also the userform that I have the code in.  Thank you. Please let me know if you get the file I've been having a problem uploading files.

Answer
Discuss

Discussion

Divinedar    Sorry but once again there are no files with your question (they should be below the question, like in your very first question). I see another user posed a new question today and successfully attached an Excel file so it seems the website is eorking as expected.

Did you see the discussion point I added to my recent reply on how to attach files? Did the revised file work for you (before you modified it)? You can download it again BTW. 
John_Ru (rep: 6142) Jan 31, '23 at 1:44 am
One user just told me they had a similar problem but that was a. csv (not permitted). Try a short file name perhaps.
John_Ru (rep: 6142) Jan 31, '23 at 2:48 am
I contacted "Don" and told him. He responded and asked if they were excel files and I said yes. Haven't heard from him since then. I just upload a file "Help File.xlsm" to this discussion by clicking edit to my question and browsed and added the file.  When I hit SUBMIT I did not see the file attached. 

So I have no idea why not. I even checked by browser to see if it was blocking in anyway. I didn't see anything with the "Exception" to disregard the "HTTPS" for that website.  I haven't tried this but that's all I saw that would probably affect the upload of a file.

So I guess since I can't upload a file I would get very little help. Thank you anyway.
Divinedar (rep: 20) Jan 31, '23 at 11:40 am
Divinedar. I've no idea why you can't upload files (when you could before). If I get chance tomorrow I will try to help you by modifying my code so it is launched from your first UserForm. No guarantees though, I have several things to do apart from volunteering my help on the Forum. 
John_Ru (rep: 6142) Jan 31, '23 at 12:46 pm
Divinedar. I checked the Forum and saw 2 new alerts on this topic but can't see that anything has been changed or added to your question nor an additional discussion point.

I modified your old file as I said and gave an Answer / file - have you tried it yet? 
John_Ru (rep: 6142) Feb 1, '23 at 4:44 pm
I was finally able to upload the file. Had to do it on my iPad so there is a problem with my laptop. Do I need to do a new question?
Divinedar (rep: 20) Feb 9, '23 at 4:01 pm
Hi Divinedar. 

Please ask a new question (I'm a bit lost as to where we are now, since my Answer code/file seemed to work well for me).

If you can't upload the file with thst question, please say so here. Either way I'll try to look soon (but I'm quite busy tomorrow so it might be the weekend).
John_Ru (rep: 6142) Feb 9, '23 at 5:40 pm
Add to Discussion

Answers

0
Selected Answer

Divinedar

It isn't enough to "... change the combobox and listbox name" in the macros I provided before, they are private to a form so need to be copied to "behind" your own UserForm . 

Given you could not upload your file, I've modified your previous file (and deleted the Demo... button added in the previous Answer).

The modified file attached has a slightly modified code to launch the second UserForm (from the "Select Products Order" button of the first), changes in bold:

Private Sub cmdOpGnPrd_Click()



'Click to open with the "Select Product Order" button



'Set screens to Full Screen



Dim xlws As XlWindowState

xlws = Application.WindowState

Application.WindowState = xlMaximized



'Show frmGunParts userform before closing frmForm

frmGunParts.Show

' close frmForm

Unload Me



Application.WindowState = xlws

End Sub

where the Customer number is added to the second form BEFORE the first closes by the initialise code, added lines in bold (and I had to declare variables since your workbook uses Option Explicit):

Private Sub UserForm_Initialize()

Dim ws As Worksheet, CT As Shape

'populate Customer No.
frmGunParts.txtCustupdate.Text = frmForm.cboCustNo.Text

' loop through sheets and set data sources
For Each ws In ThisWorkbook.Worksheets
    ' check sheet name starts with root
    If InStr(ws.Name, Root) = 1 Then
        ' add name (less Root) to ComboBox list
        Me.cmbGunSel.AddItem Split(ws.Name, Root)(1)
    End If

Next ws

End Sub

If no Customer No is selected previously, the box will be blank.

I've then  added a reddish "Submit" button to the form, to run modified code:

Private Sub Cb_Submit_Click()

Dim Picked As String, x As Long

' loop through lstGunOrder

With Me.lstGunOrder
    For x = 0 To .ListCount - 1
        ' get Item# and Parts name (e.g.) from List
        If .Selected(x) = True Then
           Picked = Picked & .List(x, 0) & " " & .List(x, 1) & vbCrLf
        End If
    Next x
End With

' tell user
MsgBox "For Customer No. " & txtCustupdate.Text & ", you picked:" & vbCrLf & vbCrLf & Picked & vbCrLf & " parts for model " & cmbGunSel.Text
Unload Me


End Sub

If you choose a model from the ComboBox and pick one or more items from the ListBox (I changed its MultiSelect property to allow that, as before), that gives a message box  saying which parts from which model were picked for which customer number.

You need to modify/ expand that to do whatever you need with that (or more) data.

Hope this helps.

Discuss

Discussion

Forgot to mention a key point above (about copying macros to form). Revised Answer to suit. 
John_Ru (rep: 6142) Feb 1, '23 at 4:51 pm
Ok so I replaced and updated my code with what you sent me to populate the 1st listbox with the parts when a worksheet is selected.  It’s not working for me.
Divinedar (rep: 20) Feb 1, '23 at 5:33 pm
THIS IS IN THE IN THE “frmGunParts” USERFORM
Option Explicit
Const Root As String = "PT-"
Private Sub Cb_Submit_Click()
Dim Picked As String, x As Long
' loop through lstSELpart (1st listbox to fill)
With Me.lstSELpart '1st listbox to fill
    For x = 0 To .ListCount - 1
        ' get Item# and Parts name (e.g.) from List
        If .Selected(x) = True Then
           Picked = Picked & .List(x, 0) & " " & .List(x, 1) & vbCrLf
        End If
    Next x
End With
' tell user
MsgBox "For Customer No. " & txtCustupdate.Text & ", you picked:" & vbCrLf & vbCrLf & Picked & vbCrLf & " parts for model " & cmbGunSel.Text
Unload Me
End Sub
Divinedar (rep: 20) Feb 1, '23 at 5:34 pm
Private Sub Cb_Submit_Click()

Dim Picked As String, x As Long
' loop through lstSELpart
With Me.lstSELpart
     For x = 0 To .ListCount - 1         ' get Item# and Parts name (e.g.) from List         If .Selected(x) = True Then            Picked = Picked & .List(x, 0) & " " & .List(x, 1) & vbCrLf         End If     Next x End With ' tell user MsgBox "For Customer No. " & txtCustupdate.Text & ", you picked:" & vbCrLf & vbCrLf & Picked & vbCrLf & " parts for model " & cmbGunSel.Text Unload Me End Sub
Divinedar (rep: 20) Feb 1, '23 at 5:46 pm
I'm sorry this is just not working for me being TeachExcel.  I don't know if it's me or the permissions I've been given to be on this site.  Don't Answer.  I'm going to find someone else to help me.  You've been patient and great it's not you it's the website.  I can't add a file. I tried to post 1 sentence and it gave me an error so I'm leaving. This is wasting your time and mine. Again thank you.
Divinedar (rep: 20) Feb 1, '23 at 5:50 pm
Sorry to hear that you're leaving, Divinedar. I've dealt with many users previously and you're the first to report such problems. Thanks for selecting my Answer and good luck. 
John_Ru (rep: 6142) Feb 2, '23 at 1:46 am
Missed your first point, with code. Can't reply on that now but did you just try the file I provided? I used your frmGunParts and it worked well for me.
John_Ru (rep: 6142) Feb 2, '23 at 1:49 am
Yes I did. It's ok I'll figure it out. I think that when I select a worksheet in combobox it's not being made active so it's not upfront.  I just wish I could upload the file. I'll figure it out.
Divinedar (rep: 20) Feb 3, '23 at 1:39 pm
Good luck 
John_Ru (rep: 6142) Feb 3, '23 at 1:58 pm
I saw 2 alerts about your question being updated but it's (very) late here and I can't see the change (or any file), sorry.
John_Ru (rep: 6142) Feb 3, '23 at 9:29 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login