Vba Code to generate highest number

[singupalli]

Hi

I need assistance.

need a code to generate auto numbers

In my data base sheet "Inventory" i have a column for Invoice numbers.

The code should verify this Column and generate the next highest number to the User form Invoice number text box.

can some one help please

Thank you
prasas

 

[OfficeUser]

I used a command button to trigger textbox to show the next available number. Not sure how your doing it.

Private Sub CommandButton1_Click()
Dim cMax As Variant
cMax = Application.WorksheetFunction.Max(Range("A:A"))
UserForm1.TextBox1.Value = cMax + 1
End Sub

You would need to change A:A to reflect the Inventory column. Also if your inventory column changes this code is not the best. You also need to update the Userform, Textbox, and command buttons names accordingly.

 

[singupalli]

Its not working,

I guess its not reading from the right worksheet
My worksheet is named "Inventory"

 

[OfficeUser]

I misread your post a bit, sorry for that. You would need to Range A:A to reflect the column which contains your invoice numbers.

Are you using any code currently?

 

[OfficeUser]

Place this code in the sheet it will be used:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cMax As Variant
cMax = Application.WorksheetFunction.Max(Range("A:A"))
Sheets("Inventory").TextBox1.Value = cMax + 1
End Sub

 

[singupalli]

Its working now.
small changes
and it done

Thank you