Selected Answer
Divinedar
(Revision- this replaces the answer posted earlieir)
You didn't add a file to your question so instead (like before) I've given you an example which should help.
In the revised file attached, I'm proposing that there's a common start for parts list sheets. This is declared as a Constant at the top of of the UserForm1 code as follows:
Const Root As String = "PT-"
It could be anything but I've made it short so it doesn't restrict the sheet names too much (hopefully). I then copied your parts sheet and renamed it with that common start (and added a copy -with different checked boxes- as "PT-Another model").
The first sheet now has a new button saying "Demo ListBox selections". Click that and new form UserForm1 is shown then initialised, populating the ComboBox (top right) with the model names (=sheet names less the Root) e.g. "Another model", as follows,with comments to help:
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
When the dropdown is chnaged (a model picked), this code populates the ListBox:
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
Change the sheet and the List will change.
Note that the List box has the property Multiselect set to 2 - fmMultiSelectExtended (which means you can control click several distinct items and/or click one and shift click another to pick those and the ones between.
When a sheet is chosen from the CombBox dropdown, the checked items are displayed as follows (I did the first 4 columns only):
When the green Submit button is clicked (after some selections), this sub delivers the selected items via this sub:
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
This code whould work with many parts list sheets (provided they have whatever Root you choose).
Hope this gives you a good idea of what to do. You might just edit and rename my UserForm1.
