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.