Need a formula to create an order number

[slorryy]

Hi People

I am trying to deign a form and excel.

I need a function to auto create a order number (basically a function/formula that add 1 onto the previous order number when a new order is created)

Hope that makes sence

Any help would be fab

Mark

[Gord Dibben]

Mark

Have a look at John McGimpsey's VBA method.

http://www.mcgimpsey.com/excel/sequentialnums.html


Gord Dibben MS Excel MVP

On Fri, 5 May 2006 17:56:40 -0500, slorryy
<[email protected]> wrote:

>
>Hi People
>
>I am trying to deign a form and excel.
>
>I need a function to auto create a order number (basically a
>function/formula that add 1 onto the previous order number when a new
>order is created)
>
>Hope that makes sence
>
>Any help would be fab
>
>Mark

Gord Dibben MS Excel MVP

[Richard Buttrey]

On Fri, 5 May 2006 17:56:40 -0500, slorryy
<[email protected]> wrote:

>
>Hi People
>
>I am trying to deign a form and excel.
>
>I need a function to auto create a order number (basically a
>function/formula that add 1 onto the previous order number when a new
>order is created)
>
>Hope that makes sence
>
>Any help would be fab
>
>Mark

Perhaps the simplest way is to hold the current order number as a
variable in a cell. Then with the event that triggers a new order, add
a line of code that increases the order number variable by 1.

So suppose your order numbers are are of the form ON1234, ON1235 etc,
where there is a prefix "ON" to a sequential number, and the variable
cell is named say "Onumber"

add a line of code

Range("Onumber")= "ON" & Right(Range("Onumber"), Len(Range("Onumber"))
- 2) + 1


HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[slorryy]

On Fri, 5 May 2006 17:56:40 -0500, slorryy
<[email protected]> wrote:

>
>Hi People
>
>I am trying to deign a form and excel.
>
>I need a function to auto create a order number (basically a
>function/formula that add 1 onto the previous order number when a new
>order is created)
>
>Hope that makes sence
>
>Any help would be fab
>
>Mark

Perhaps the simplest way is to hold the current order number as a
variable in a cell. Then with the event that triggers a new order, add
a line of code that increases the order number variable by 1.

So suppose your order numbers are are of the form ON1234, ON1235 etc,
where there is a prefix "ON" to a sequential number, and the variable
cell is named say "Onumber"

add a line of code

Range("Onumber")= "ON" & Right(Range("Onumber"), Len(Range("Onumber"))
- 2) + 1


HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi Richard, Sounds like that will work.

But the only thing i cant work out is what event will trigger the new order. I was hopeing the new order number would be triggered when the file is opened. can you think of another way to do it?

The file i am working on is here if you wish to have a look http://www.slorryy.com/tekbo.xls

also gord thanks from the info, but i dont know VB boo hoo

Thanks

[Richard Buttrey]

On Sat, 6 May 2006 18:54:52 -0500, slorryy
<[email protected]> wrote:

>
>Richard Buttrey Wrote:
>> On Fri, 5 May 2006 17:56:40 -0500, slorryy
>> <[email protected]> wrote:
>>
>> >
>> >Hi People
>> >
>> >I am trying to deign a form and excel.
>> >
>> >I need a function to auto create a order number (basically a
>> >function/formula that add 1 onto the previous order number when a new
>> >order is created)
>> >
>> >Hope that makes sence
>> >
>> >Any help would be fab
>> >
>> >Mark
>>
>> Perhaps the simplest way is to hold the current order number as a
>> variable in a cell. Then with the event that triggers a new order, add
>> a line of code that increases the order number variable by 1.
>>
>> So suppose your order numbers are are of the form ON1234, ON1235 etc,
>> where there is a prefix "ON" to a sequential number, and the variable
>> cell is named say "Onumber"
>>
>> add a line of code
>>
>> Range("Onumber")= "ON" & Right(Range("Onumber"), Len(Range("Onumber"))
>> - 2) + 1
>>
>>
>> HTH
>> __
>> Richard Buttrey
>> Grappenhall, Cheshire, UK
>> __________________________
>
>Hi Richard, Sounds like that will work.
>
>But the only thing i cant work out is what event will trigger the new
>order. I was hopeing the new order number would be triggered when the
>file is opened. can you think of another way to do it?
>
>The file i am working on is here if you wish to have a look
>http://www.slorryy.com/tekbo.xls
>
>Thanks


Yes the Workbook Open event could trigger the new order number. But
what then? Are you eventually going to save the new order with a new
name or just print it?

Either way you would need to save immediately the blank order that you
open as soon as the workbook open event has triggered the change of
number. That way the next time you open the order it will continue to
open with the last number already in place.

So in the VBA Project window, expand the Microsoft Excel Objects list
by clicking on the '+' sign, double click on the ThisWorkbook object,
and over on the right hand side of the VBA window, select the Workbook
Object from the left hand drop down and then the 'Open' event from the
right hand drop down window. Now enter the following code.

Private Sub Workbook_Open()
Range("Onumber") = Range("Onumber") + 1
ActiveWorkbook.Save
End Sub

I have named the cell G6 on Sheet1of your tekbo.xls template as
"Onumber"

HTH - let me know if not.


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[slorryy]

Hi Richard

Thanks for your help regarding this subject, I have decided to just enter the numbers manually as I am not educated inVB.

Cheers for your time.