Selected Answer
Hi Divinedar
The error arises here:
If CB_lstSELpart.Value = False Then
because you deleted the turquoize checkbox CB_lstSELpart (labelled "List only checked items") used in UserForm frmGunParts in previous versions of your file. Given you declared Option Explicit at the UserForm level, VBA can't find CB_lstSELpart as a UserForm control nor as a variable so it gives the error.
The easy solution is to (block) comment out the code you now don't need / won't use, as in bold below (for learning purposes) or delete it:
Private Sub cmbGunSel_Change()
Dim Index As Long, Col As Long, Val As Variant, CT As Object
Dim lRw As Long, Rw As Long
' 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.cmbGunSel.Value Then Exit Sub
' with chosen source
With ThisWorkbook.Worksheets(Root & Me.cmbGunSel.Value)
'determine last used row in B
lRw = .Cells(.Rows.Count, 2).End(xlUp).Row
' loop down rows
For Rw = 3 To lRw
If IsEmpty(.Cells(Rw, 2)) = False Then
Index = 0
With .Cells(Rw, 1)
' ' populate the list based on the option
' If CB_lstSELpart.Value = False Then
' add an item to the lstGunOrder list
Me.lstSELpart.AddItem .Offset(0, 1).Text
Index = 1
' Else
' ' loop through shapes to see if a checked one in on column A or the row
' For Each CT In .Parent.Shapes
' ' find if there's a checked box in this cell
' If Not Intersect(.Range(CT.TopLeftCell.Address), .Cells(Rw, 1)) Is Nothing _
' And InStr(CT.Name, "Check Box") = 1 _
' And CT.OLEFormat.Object.Value = 1 _
' Then
' ' if checked add an item to the lstGunOrder list
' Debug.Print CT.TopLeftCell.Address
' Me.lstSELpart.AddItem .Offset(0, 1).Text
' Index = 1
' End If
' Next CT
' End If
' only if an item was added, populate first four columns of list
If Index = 1 Then
' loop through next 3 columns and add to liost row
For Col = 2 To 4
Val = .Offset(0, Col).Value
' format the currency column
If Col = 4 Then Val = Format(Val, "$#,##0.00")
'add to column
Me.lstSELpart.List(Me.lstSELpart.ListCount - 1, Index) = Val
' increase the column index
Index = Index + 1
Next Col
End If
End With
End If
Next Rw
End With
' set up LB2 columns
Me.lstSELpart.ColumnCount = 3
Me.lstSELpart.ColumnWidths = "40;200;40"
sh.Cells(iRow + 1, 15) = cmbGunSel.Value
End Sub
This is done in the first attached revised file.
Revision 16 March 2023:
Regarding your comment below the question and your Invoice Number code, don't put it in a control _Change macro (I had to block comment out yours so it didn't overwrite the value created by the code below). It should go elsewhere e.g. in the second attahced file below, I put it in the UserForm_Initialize code so it appears with the form (changes in bold, including correcting your column number for determining iRow- was 2 not 1):
Private Sub UserForm_Initialize()
Dim CustOrd As String
Dim InvNo As String
<< existing code>>.
Next ws
' add an invoice number
Set sh = ThisWorkbook.Sheets("Invoice_History")
With sh
' determine last used row for column A
iRow = .Cells(.Rows.Count, 1).End(xlUp).Row
' write new order number
InvNo = "INV" & Format(iRow - 1, "000")
' write to cell (or do you want to wait until everything is complete?)
.Cells(iRow + 1, 1) = InvNo
'Show on userform
Me.txtInvno.Text = InvNo
End With
End Sub
Hope this sorts this problem. Also, we're all in learning mode so no need to apologise!