Create autonumber when adding data into sheet

[daza110]

I have a spreadsheet with 4 Sheets in atm.
"Home" "Create Order" "Supplier List" "Products"

On my "Create Order" Sheet I have a Button to open a userform to add a new product. I have a combo box which allows me to select the Supplier too. This works fine. What I am looking for is rather than me having to remember the last Product number, I was hoping to create an Auto Number system. mry code for the userform is below:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("Products")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
  
lPart = Me.txtSupplier.ListIndex

'check for a product number
If Trim(Me.txtProduct.Value) = "" Then
  Me.txtProduct.SetFocus
  MsgBox "Please enter a product number"
  Exit Sub
End If

'copy the data to the database
With ws
  .Cells(iRow, 1).Value = Me.txtProduct.Value
  .Cells(iRow, 2).Value = Me.txtProductName.Value
  .Cells(lRow, 3).Value = Me.txtSupplier.Value
  .Cells(iRow, 4).Value = Me.txtSupplier.List(lPart, 1)
  .Cells(iRow, 5).Value = Me.txtDate.Value
  .Cells(iRow, 6).Value = Me.txtCost.Value
  .Cells(iRow, 7).Value = Me.txtQuantity.Value
  .Cells(iRow, 8).Value = Me.txtQuantityPerLot.Value
End With

'clear the data
Me.txtProduct.Value = ""
Me.txtProductName.Value = ""
Me.txtSupplier.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtCost.Value = ""
Me.txtQuantity.Value = ""
Me.txtQuantityPerLot.Value = ""
Me.txtProduct.SetFocus

End Sub

Private Sub cmdClose_Click()
  Unload Me
End Sub


Private Sub UserForm_Initialize()
Dim cPart As Range
Dim ws As Worksheet
Set ws = Worksheets("Supplier List")

For Each cPart In ws.Range("SupplierID")
  With Me.txtSupplier
    .AddItem cPart.Value
    .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
  End With
Next cPart

Me.txtProduct.Value = ""
Me.txtProductName.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtCost.Value = ""
Me.txtQuantity.Value = ""
Me.txtQuantityPerLot.Value = ""
Me.txtProduct.SetFocus

End Sub

Any help would be greatly appreciated

 

[daza110]

Anybody?

 

[Trevor G]

Can you add a line to take the previouse cell value and Add 1

so something like this

Sub addrow()
Range("A1").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
End Sub

 

[Misca]

Here's a couple of ways I can come up with right away:

You can use workbook names to store the value:

'This one saves the value as a name:
ThisWorkbook.Names.Add Name:="PrtValue", RefersTo:=Me.TextBox1.Value

This way the value will be remembered even if the workbook closes if desired.

Instead of a workbook name you could also store the value as a Public variable.

Or instead of closing the userform you could simply hide it (= change "Unload Me" to "Me.Hide") and when you show the userform again, all the values you want to keep would still be there.

 

[daza110]

Thanks for your suggestions but unfortunately these didnt work. Im kind of new to this so i dont know if i did it right but any other suggestions would be great.

 

[daza110]

Partly Solved the issue in my code above (below)

'clear the data
Me.txtProduct.Value = "=ROW()-1"
Me.txtProductName.Value = ""
Me.txtSupplier.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtCost.Value = ""
Me.txtQuantity.Value = ""
Me.txtQuantityPerLot.Value = ""
Me.txtProduct.SetFocus

I have set the default value when I clear the data to be =ROW()-1. This works.

My new problem is actually getting it to be the default value when I open the form as new.

open to suggestions please on making =ROW()-1 to be the default value when i open the form. Thanks

 

[daza110]

Questions answered both times thanks to my amazingly smart girlfriend... Who knows nothing about VBA... How gutted do i feel... =] Thanks for posts tho guys.