Auto number textbox value when worksheet is initialized.

Christiaan Viljoen

New Member
Joined
Oct 2, 2014
Messages
11
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.
 
Last edited:

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can use eg:

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

Christiaan Viljoen

New Member
Joined
Oct 2, 2014
Messages
11
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
 

Christiaan Viljoen

New Member
Joined
Oct 2, 2014
Messages
11

ADVERTISEMENT

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

Christiaan Viljoen

New Member
Joined
Oct 2, 2014
Messages
11

ADVERTISEMENT

It gives the runtime error 9 subscript out of range
 

Christiaan Viljoen

New Member
Joined
Oct 2, 2014
Messages
11
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
 
Last edited:

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,054
Messages
5,526,510
Members
409,705
Latest member
MB1984

This Week's Hot Topics

Top