Auto number textbox value when worksheet is initialized.

[Christiaan Viljoen]

Hey guys.... I've got a userform 2 that is used to input values and data into a invoice that has been created on a worksheet named invoice. All data entered on this userform will also be saved on a worksheet named statements for filtering later on... What I need is the invoice number to be updated automatically when the userform is opened. I. Would like to retreive this number from the statements worksheet like column a will contain all invoice numbers in order. When a invoice is saved it needs to save it with the number in next open row and then retrieve that number 1 next time the userform opens. Hope it makes sense... Thanks a lot. I now realize I said worsheet on the title instead of userform. Sorry for that.

 

[Andrew Poulsom]

You can use eg:

TextBox1.Text = WorksheetFunction.Max(Worksheets("Sheet1").Columns(1)) + 1

 

[Christiaan Viljoen]

Hey andrew. Nothing happens... I'm not sure where to enter the coding. If I put it under userform initialize it gives me run-time error '9': subscript out of range

 

[Andrew Poulsom]

Do you have a worksheet named Sheet1? Adjust to suit.

 

[Christiaan Viljoen]

I changed it to sheet6... It now shows
Private sub userform_initialize()
Dim n as integer
Do
N=n+1
Combobox1.additem sheets.name
Loop until n = worksheets.count
Textbox1.text=worksheetfunctions.max(worksheets("sheet6").columns(1))+1
End sub

 

[Andrew Poulsom]

And what happens? It's WorksheetFunction not Worksheetfunctions.

 

[Christiaan Viljoen]

It gives the runtime error 9 subscript out of range

 

[Andrew Poulsom]

Do you have a worksheet name sheet6?

 

[Christiaan Viljoen]

Yes I do.. That's where I want to get the invoice number from.... It has values in a1 to a6 so my plan is to take a6 value +1 for the invoice number and when the invoice save it puts the data in next open row witch will be a7 then next invoice it need to call a7 +1 and so on

 

[Andrew Poulsom]

You can't have a worksheet named sheet6 if you get a subscript error. The code uses the Name (what's on the sheet's tab) not the CodeName.