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

Unexplainable Variable Error

0

So it worked until I added the following code trying to create a dynamic invoice number as the same way an "ORDER" and "CUSTOMER" number was created.  Been trying different ways to create this. No problem creating it just not sure where to put it. 

Private Sub txtInvno_Change()
    Dim Invno As String
    Dim sht As Worksheet

    Set sht = ThisWorkbook.Sheets("Invoice_History")


    With sht
        ' determined last used row here, with different method
        iRow = .Cells(.Rows.Count, 2).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

But when I entered just that code I get the error "Variable Not Defined" in this code at this spot:

Private Sub cmbGunSel_Change()

...
            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
                    
...

End Sub

I made no change to any other code just added a code and now I get the error. I delete out the code I added, close file and reopen and still get the error. 

Sorry for being such a pain but I'm in learn mode.  Thank you in advance.

Answer
Discuss

Discussion

Regarding my first code "txtInvno_Change()" in my question; how do I get this to trigger or do I have it as a wrong "Sub" type?  I want the Invoice number to appear on the"frmGunParts" userform when shows?
AAlso I tried calling it in the "Private Sub UserForm_Initialize()" but it didn't work.  So I guess I'm asking where exactly do I put it or how do I include it in the module or make a new module and call it from there?
Divinedar (rep: 20) Mar 15, '23 at 1:38 pm
Hi. Sorry, I just looked to sort out your error (which I think I did). 

Looks like you want to create an new Invoice Number but when? When the form is launched?

You shouldn't write it as a control change macro but add it to the existing UserForm_Initialize macro. Need me to do that too?

(BTW you used CODE around your text- not needed!)
John_Ru (rep: 6102) Mar 15, '23 at 1:45 pm
Have revised my answer and attached a second file.
John_Ru (rep: 6102) Mar 15, '23 at 2:02 pm
Add to Discussion

Answers

0
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!

Discuss

Discussion

Perfect. Thank you. I have to say I now understand more than ever due to your expanations. Again thank you. You explain very well.
Divinedar (rep: 20) Mar 15, '23 at 4:01 pm
Great! Thanks for your kind words and for selecting my Answer, Divinedar. 
John_Ru (rep: 6102) Mar 15, '23 at 4:29 pm
BTW I didn't have time to do it but suggest you consider adding an invoice number only when the first part is added to the order. 
John_Ru (rep: 6102) Mar 15, '23 at 6:29 pm
Add to Discussion


Answer the Question

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