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

Dynamic numbering not working properly

0

I was able to get the "ORD#" to appear in the "txtOrderNo" textbox but my problem is I can't get it to start at "ORD001" and to start at the first empty cell on the worksheet "Customer_Order_History". It appears on the worksheet but not numbered correctly and not starting at the correct empty cell.  Also need to know do I have the code in the correct module so I need to know if that is my problem also.

Private Sub UserForm_Initialize()

Dim ws As Worksheet, CT As Shape
    Dim sh As Worksheet
    Dim iRow As Long
    Dim myRange As Range, f As Range ' ### aded these
    Set sh = ThisWorkbook.Sheets("Customer_Order_History")

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

    iRow = [Counta(Customer_Order_History!B3:B)] + 1 ' moved this

'    ' ### added this
    Set myRange = Worksheets("Customer_Order_History").Range("B3:B500")
    
'    ' changed this to add form number
    Set f = myRange.Find(What:=frmGunParts.cmbGunSel.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False) '<--| try and find combobox selected value

    If Not f Is Nothing Then
        iRow = f.Row
    End If
    
' loop through sheets and set data sources
    With sh
        ' replaced this
        .Cells(iRow, 3) = "ORD" & Format(iRow, "000") 'New Dynamic Serial Number
    End With

'Show on userform
Me.txtOrderNo.Text = sh.Cells(iRow, 3) 'New Dynamic Serial Number
    
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 attached a file so please let me know if you get the file. I have been running into issues to add files to my questions. I figured it out. Thank you.

Answer
Discuss

Discussion

Hi Divinedar,
Nope, no file is attached.
WillieD24 (rep: 537) Mar 11, '23 at 11:06 am
Hi again Divinedar  

Sorry but there's no file attached to your question (as you suggested)..   I looked at the file Gun Restoring Database with multiselect ListBox of checked items v0_f (from my answer in January) but I don't have that TextBox and there's no order history in the sheet you refer to.  

Please try to attach the file again, as follows- go to your question, click the Edit hyperlink (below bottom right),  go to the bottom and click Add Files to the Post button (above Cancel). That should open up a section headed Files- click the upper Choose File button, select your Excel file, click Ok (the name file should now appear in that upper position) then click Submit Post.

Go back to your question and check the file is attached. If it isn't, please let us know how your file / UserForms differ from the file I mentioned above.

Sorry for the hassle. Nothing I can do about it unfortunately.
John_Ru (rep: 6092) Mar 11, '23 at 11:33 am
I figured out why. Thank you see attachment.
Divinedar (rep: 20) Mar 11, '23 at 12:02 pm
Thanks, will do
John_Ru (rep: 6092) Mar 11, '23 at 12:06 pm
Took a look but I'm not clear what you're try to do when that form is launched. Please edit the question to explain. Also what's meant to happen if there are several entries in worksheet "Customer_Order_History"?
John_Ru (rep: 6092) Mar 11, '23 at 12:13 pm
First on the customer information form select a customer or enter new customer info select "save".

Second select "Select Product Order" and the "frmGunParts" userform appears.

The textbox txtOrderNo  "Order Number" won't start with "ORD001" and if you look at the worksheet "Customer_Order_History" it skips a row.

Lets start there first.  Also I need for that "Order Number" to continue on each entry until I click "ORDER COMPLETE" then clear. Next order to start with the next new "Order Number".
Divinedar (rep: 20) Mar 11, '23 at 12:29 pm
I corrected this to column "B"

' loop through sheets and set data sources
    With sh
        ' replaced this
        .Cells(iRow, 2) = "ORD" & Format(iRow, "000") 'New Dynamic Serial Number
    End With
Divinedar (rep: 20) Mar 11, '23 at 12:33 pm
I want to "want to wait until everything is complete". I have the "ORDER COMPLETE" button because there will be more than one row to the order.  I need it to stay that Order Number until I click "ORDER COMPLETE".
Divinedar (rep: 20) Mar 11, '23 at 1:22 pm
Divinedar. That comment should really be in your question or against my Answer. Nevertheless I've addressed it in the revised answer and changed file I've just uploaded.
John_Ru (rep: 6092) Mar 11, '23 at 2:07 pm
I don't understand which "Discuss" key I should select. But this is perfect for now. I think I can figure out the rest. Thank you. Appreciate your patience. Till next time.
Divinedar (rep: 20) Mar 11, '23 at 2:41 pm
Glad that worked for you, Divinedar.

If your comment is against an Answer, please use the Discuss/ Add to Discussion button below that Answer. If you're adding fundemental details about your question however, edit the question instead. That's my opinion but please check the blue Rules hyperlink under the green banner above.
John_Ru (rep: 6092) Mar 11, '23 at 3:09 pm
Add to Discussion

Answers

0
Selected Answer

Divinedar

If you just want sequential order numbers based on the worksheet "Customer_Order_History", the modified code below should do it (changed in bold):

Public CustOrd As String
Private Sub UserForm_Initialize()

    Dim ws As Worksheet, CT As Shape
    Dim sh As Worksheet
    Dim iRow As Long

    Set sh = ThisWorkbook.Sheets("Customer_Order_History")

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

    ' ### scrapped this
    'iRow = [Counta(Customer_Order_History!B3:B)] + 1 ' moved this

    With sh
        ' determined last used row here, with different method
        iRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        ' write new order number
        CustOrd = "ORD" & Format(iRow - 1, "000")
        ' write to cell (or do you want to wait until everything is complete?)
        .Cells(iRow + 1, 2) = CustOrd
        'Show on userform
        Me.txtOrderNo.Text = CustOrd
    End With

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

Revision 11 March 2023:

Following your later commet (under your question, not here), I declared the new variable CustOrd outside the macro so it's also available to other procedures on that form. In particular, it's in the code below which I added to your Order Complete button in the revised file attached: 

Private Sub CommandButton1_Click()



Dim iRow As Long



With ThisWorkbook.Sheets("Customer_Order_History")

        iRow = .Cells(.Rows.Count, 2).End(xlUp).Row

        'write order number to sheet

        .Cells(iRow + 1, 2) = CustOrd

        ' then write other values from form...



End With

' tell user and close form
MsgBox "New order added"
Unload Me

End Sub

You'll see that just writes the new number to the sheet (leaving you to add other details on that line).

Hope this helps.

Discuss

Discussion

Thanks for selecting my Answer, Divinedar.
John_Ru (rep: 6092) Mar 11, '23 at 3:05 pm
Add to Discussion


Answer the Question

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