Auto Generation of number in sequential manner in textbox

[av123]

Hi
I am new to VBA. I have to auto generate serial number in a textbox when the user enters the form. The number should be stored somewhere so that when the form is closed and is again opened it does not start with 1 again. Also, example the number generated is 5. So, whatever data the user entered should be placed in row 5 of the excel sheet. I know how to do that but the number generation is not happening.
I searched a lot and even found links that could be called as duplicate of mine but nothing helped.
Please help

[Neil_]

Hi,
here's one I made earlier.

If it crashes complaining of a missing reference, there is a fix here - http://stackoverflow.com/questions/1...rform#17493134

[Bernie Deitrick]

If you want to place data into the first blank row, just use code like this:

Please Login or Register  to view this content.

[av123]

Hi.
Thanks for the code but this part I know. I want to know how to generate serial number and print it in a textbox. once the form is closed it should start with the next number and not go to the first number.

[Bernie Deitrick]

Then in the userform initialize event, Name a cell "SNumber" and use code like

Please Login or Register  to view this content.

[Neil_]

Or if you don't want to reference a worksheet....

Please Login or Register  to view this content.

[Neil_]

or... You could leave this line out of Bernie's code if you set the Textbox controlSouce property to SNumber

Please Login or Register  to view this content.

[av123]

Hi,
Its not working

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim full As String
Dim r As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

r = Me.txtNum.Value
Cells(r, 1).Select

Call UserForm_Initialize
End Sub

Private Sub UserForm_Initialize()
With txtNum
.Value = Format(Val(Cells(Rows.Count, 1).End(xlUp)) + 2)
.Enabled = False
End With
End Sub

Now the problem is that the number is not being incremented.
Please help

[Bernie Deitrick]

The number will not increment until you put a value into the cell at the bottom of column A. You are basically counting the used rows based on column A....

[av123]

Thank You! I was able to do it with your help!