Auto increment two cells with userform

[obiwann]

I am trying to figure out to have two cells increment after creating a record with my user form.
My form is simple where I have a button that will create a row every time it is clicked. After clicking the close button the data will sort from highest to lowest trace number.
Lets say for example my 1st trace number will start at 190000 and the invoice number is 9000, I need the form to generate a these numbers to increase by 1 every time the user clicks the create order button.

Can anyone please help me with this?

Thanks

 

[DanteAmor]

Can you specify in which columns those numbers will be?
You can also put your code of the button that creates a row.

 

[obiwann]

Is there a way I can send you my excel file?

 

[obiwann]

It would be column C & D and my headers are on Row 7.
Here is my code:

Dim currentrow As Long





Private Sub cmdAdd_Click()
    'Copy input values to sheet.
    
    
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Order Entry")
    iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
   
        ws.Cells(iRow, 1).Value = Me.txtDate.Value
        ws.Cells(iRow, 2).Value = Me.txtCustomer.Value
        ws.Cells(iRow, 3).Value = Me.txtTrace.Value
        ws.Cells(iRow, 4).Value = Me.txtInvoiceNo.Value
        ws.Cells(iRow, 5).Value = Me.txtCustomerPo.Value
        ws.Cells(iRow, 6).Value = Me.cboPartType.Value
        ws.Cells(iRow, 7).Value = Me.cboSupplier.Value
        ws.Cells(iRow, 8).Value = Me.txtInvDate.Value
        ws.Cells(iRow, 9).Value = Me.txtAmount.Value
        ws.Cells(iRow, 10).Value = Me.txtDateReq.Value
        ws.Cells(iRow, 11).Value = Me.txtPaid.Value
   Call UserForm_Initialize
  
 
    
End Sub

 

[DanteAmor]

It would be column C & D and my headers are on Row 7.
Here is my code:

If I understood correctly, cell C and D will now be calculated, then review the following, I hope it solves your need.

Dim currentrow As Long


Private Sub cmdAdd_Click()
    'Copy input values to sheet.
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Order Entry")
    iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        
    ws.Cells(iRow, 1).Value = Me.txtDate.Value
    ws.Cells(iRow, 2).Value = Me.txtCustomer.Value
[COLOR=#0000ff]    If iRow = 8 Then[/COLOR]
[COLOR=#0000ff]        ws.Cells(iRow, "C").Value = 190000[/COLOR]
[COLOR=#0000ff]        ws.Cells(iRow, "D").Value = 9000[/COLOR]
[COLOR=#0000ff]    Else[/COLOR]
[COLOR=#0000ff]        ws.Cells(iRow, "C").Value = ws.Cells(iRow - 1, "C").Value + 1[/COLOR]
[COLOR=#0000ff]        ws.Cells(iRow, "D").Value = ws.Cells(iRow - 1, "D").Value + 1[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
    ws.Cells(iRow, 5).Value = Me.txtCustomerPo.Value
    ws.Cells(iRow, 6).Value = Me.cboPartType.Value
    ws.Cells(iRow, 7).Value = Me.cboSupplier.Value
    ws.Cells(iRow, 8).Value = Me.txtInvDate.Value
    ws.Cells(iRow, 9).Value = Me.txtAmount.Value
    ws.Cells(iRow, 10).Value = Me.txtDateReq.Value
    ws.Cells(iRow, 11).Value = Me.txtPaid.Value
    Call UserForm_Initialize
End Sub

 

[obiwann]

Wow! That works great.
But now my next and previous code does nor work properly. See code below.

Private Sub cmdNext_Click()Dim Lastrow As Long
Lastrow = Sheets("Order Entry").Range("a" & Rows.Count).End(xlUp).Row


If currentrow = Lastrow Then
MsgBox "You are viewing the last row of data", vbCritical
Else
currentrow = currentrow + 1
txtDate.Value = Cells(currentrow, 1)
txtCustomer.Value = Cells(currentrow, 2)
txtTrace.Value = Cells(currentrow, 3)
txtInvoiceNo.Value = Cells(currentrow, 4)
txtCustomerPo.Value = Cells(currentrow, 5)
cboPartType.Value = Cells(currentrow, 6)
cboSupplier.Value = Cells(currentrow, 7)
txtInvDate.Value = Cells(currentrow, 8)
txtAmount.Value = Cells(currentrow, 9)
txtDateReq.Value = Cells(currentrow, 10)
txtPaid.Value = Cells(currentrow, 11)
End If


End Sub

Private Sub cmdPrevious_Click()currentrow = currentrow - 1
If currentrow > 1 Then
txtDate.Value = Cells(currentrow, 1)
txtCustomer.Value = Cells(currentrow, 2)
txtTrace.Value = Cells(currentrow, 3)
txtInvoiceNo.Value = Cells(currentrow, 4)
txtCustomerPo.Value = Cells(currentrow, 5)
cboPartType.Value = Cells(currentrow, 6)
cboSupplier.Value = Cells(currentrow, 7)
txtInvDate.Value = Cells(currentrow, 8)
txtAmount.Value = Cells(currentrow, 9)
txtDateReq.Value = Cells(currentrow, 10)
txtPaid.Value = Cells(currentrow, 11)
End If


If currentrow = 7 Then
MsgBox "Now you are in the header row!", vbCritical
currentrow = currentrow + 1
End If


End Sub

 

[DanteAmor]

Wow! That works great.
But now my next and previous code does nor work properly. See code below.

I do not know why at the end of "cmdAdd" you have this:

[COLOR=#333333]Call UserForm_Initialize[/COLOR]

I think it should be:

[COLOR=#333333]currentrow = [/COLOR][COLOR=#333333]iRow[/COLOR]

Or add the line.