auto number a form

[alexanderd]

having created a user form i am having difficulties in finding a way to auto number each form as it is opened.
each time the form is used it inputs the data to an excel worksheet, at present the numbering is done manually.

can anyone suggest a way in which this could be done automatically

[AlfD]

Hi!

You could try the easy way of putting the counter in a cell on your worksheet and incrementing it each time you add a record. Then you can access the cell next time to work out your next number.

Alf

[alexanderd]

your suggestion is something i had not thought about. and i will work on it
what i had been working on is'

Private Sub tbxAccNo_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If tbxAccNo.Value <> vbNullString Then
' TextBox31.Value = " HELLO" ######### this works

TextBox31.Value.FormulaR1C1 = "=COUNTA(C[-8])" ' ##### this dose't

End If

End Sub
what i am attempting to do is the textbox "tbxAccNo" has value and by using that to count the noumber of rows, where am i going wrong!!!!!!!!

[AlfD]

Hi!

Not an area I'm too familiar with and I don't recognise some of the constructs.

Does textbox support .value.formula?
Does textbox have an Exit event?
Is COUNTA() useful if its argument is a single cell?

My simple approach was, in effect, to create a non-volatile memory cell which would just clock the events by adding 1 each time.

Alf