Help!

how do I increment a numbering system in excel

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Excel General (archive) RSS
Next:  Protecting Sheet to Prevent Viewing At All  
Author Message
Allan
External


Since: Oct 27, 2004
Posts: 49



PostPosted: Fri Jul 08, 2005 10:11 am    Post subject: how do I increment a numbering system in excel
Archived from groups: microsoft>public>excel>misc (more info?)

I wish to set up a Purchase Order system using Excel and i would like the PO
number to automatically increment. Can anyone pleaseadvise me how to do this?
Back to top
Allan
External


Since: Oct 27, 2004
Posts: 49



PostPosted: Fri Jul 08, 2005 11:56 am    Post subject: RE: how do I increment a numbering system in excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi Norman

With your assistance I have solved the problem.
Many Thanks
Allan



"Allan" wrote:

> I wish to set up a Purchase Order system using Excel and i would like the PO
> number to automatically increment. Can anyone pleaseadvise me how to do this?
Back to top
Norman Jones
External


Since: Sep 14, 2004
Posts: 594



PostPosted: Fri Jul 08, 2005 6:18 pm    Post subject: Re: how do I increment a numbering system in excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi Allan,

See J.E. McGimpsey's suggestions at:


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

---
Regards,
Norman



"Allan" wrote in message

>I wish to set up a Purchase Order system using Excel and i would like the
>PO
> number to automatically increment. Can anyone pleaseadvise me how to do
> this?
Back to top
Allan
External


Since: Oct 27, 2004
Posts: 49



PostPosted: Fri Jul 08, 2005 6:18 pm    Post subject: Re: how do I increment a numbering system in excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi Norman

Sorry but I do not fully understand the macro.

I have an Excel wookbook and in worksheet named "Sheet1" in cell I6 I wish
to insert an new order number each time the workbook is opened that is the
last PO number +1.

I cannot see what to do with the link that you kindly sent to me.

Can you help me further?
Cheers
Allan


"Norman Jones" wrote:

> Hi Allan,
>
> See J.E. McGimpsey's suggestions at:
>
>
> http://www.mcgimpsey.com/excel/udfs/sequentialnums.html
>
> ---
> Regards,
> Norman
>
>
>
> "Allan" wrote in message
>
> >I wish to set up a Purchase Order system using Excel and i would like the
> >PO
> > number to automatically increment. Can anyone pleaseadvise me how to do
> > this?
>
>
>
Back to top
Norman Jones
External


Since: Sep 14, 2004
Posts: 594



PostPosted: Fri Jul 08, 2005 7:48 pm    Post subject: Re: how do I increment a numbering system in excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi Allan,

If you are unfamiliar with macros, see the notes by JE McGimpsey on another
page of his site:

http://www.mcgimpsey.com/excel/index.html#usingvba

Also see David McRitchie's 'Getting Started With Macros And User Defined
Functions at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you are still experiencing problems, post back.


---
Regards,
Norman



"Allan" wrote in message

> Hi Norman
>
> Sorry but I do not fully understand the macro.
>
> I have an Excel wookbook and in worksheet named "Sheet1" in cell I6 I wish
> to insert an new order number each time the workbook is opened that is the
> last PO number +1.
>
> I cannot see what to do with the link that you kindly sent to me.
>
> Can you help me further?
> Cheers
> Allan
>
>
> "Norman Jones" wrote:
>
>> Hi Allan,
>>
>> See J.E. McGimpsey's suggestions at:
>>
>>
>> http://www.mcgimpsey.com/excel/udfs/sequentialnums.html
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Allan" wrote in message
>>
>> >I wish to set up a Purchase Order system using Excel and i would like
>> >the
>> >PO
>> > number to automatically increment. Can anyone pleaseadvise me how to do
>> > this?
>>
>>
>>
Back to top
Fungusfreakland
External


Since: Aug 30, 2006
Posts: 3



PostPosted: Wed Aug 30, 2006 12:24 pm    Post subject: Re: how do I increment a numbering system in excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

"Norman Jones" wrote:

> Hi Allan,
>
> If you are unfamiliar with macros, see the notes by JE McGimpsey on another
> page of his site:
>
> http://www.mcgimpsey.com/excel/index.html#usingvba
>
> Also see David McRitchie's 'Getting Started With Macros And User Defined
> Functions at:
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> If you are still experiencing problems, post back.
>
>
> ---
> Regards,
> Norman
>
>
>
> "Allan" wrote in message
>
> > Hi Norman
> >
> > Sorry but I do not fully understand the macro.
> >
> > I have an Excel wookbook and in worksheet named "Sheet1" in cell I6 I wish
> > to insert an new order number each time the workbook is opened that is the
> > last PO number +1.
> >
> > I cannot see what to do with the link that you kindly sent to me.
> >
> > Can you help me further?
> > Cheers
> > Allan
> >
> >
> > "Norman Jones" wrote:
> >
> >> Hi Allan,
> >>
> >> See J.E. McGimpsey's suggestions at:
> >>
> >>
> >> http://www.mcgimpsey.com/excel/udfs/sequentialnums.html
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >>
> >> "Allan" wrote in message
> >>
> >> >I wish to set up a Purchase Order system using Excel and i would like
> >> >the
> >> >PO
> >> > number to automatically increment. Can anyone pleaseadvise me how to do
> >> > this?
> >>
> >>
> >>
>
>
>
Back to top
Fungusfreakland
External


Since: Aug 30, 2006
Posts: 3



PostPosted: Wed Aug 30, 2006 12:27 pm    Post subject: Re: how do I increment a numbering system in excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi Norman,
I am working with the VBA code that you've refered many people to and it
kinda works.
The date function works however the sequential numbering is not working.
I've looked at the code over and over again and there are no errors according
to the editor.

I'm using Excel 2000 and trying to create my own invoice template.

below is the code i'm using.

Thank you

Ken


Sub INVOICE_NUMBER()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "Invoice"
Const sKEY As String = "Invoice_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long

With ThisWorkbook.Sheets("Invoice")
With .Range("E3")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("E4")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY,
nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "0000")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With



"Norman Jones" wrote:

> Hi Allan,
>
> If you are unfamiliar with macros, see the notes by JE McGimpsey on another
> page of his site:
>
> http://www.mcgimpsey.com/excel/index.html#usingvba
>
> Also see David McRitchie's 'Getting Started With Macros And User Defined
> Functions at:
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> If you are still experiencing problems, post back.
>
>
> ---
> Regards,
> Norman
>
>
>
> "Allan" wrote in message
>
> > Hi Norman
> >
> > Sorry but I do not fully understand the macro.
> >
> > I have an Excel wookbook and in worksheet named "Sheet1" in cell I6 I wish
> > to insert an new order number each time the workbook is opened that is the
> > last PO number +1.
> >
> > I cannot see what to do with the link that you kindly sent to me.
> >
> > Can you help me further?
> > Cheers
> > Allan
> >
> >
> > "Norman Jones" wrote:
> >
> >> Hi Allan,
> >>
> >> See J.E. McGimpsey's suggestions at:
> >>
> >>
> >> http://www.mcgimpsey.com/excel/udfs/sequentialnums.html
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >>
> >> "Allan" wrote in message
> >>
> >> >I wish to set up a Purchase Order system using Excel and i would like
> >> >the
> >> >PO
> >> > number to automatically increment. Can anyone pleaseadvise me how to do
> >> > this?
> >>
> >>
> >>
>
>
>
Back to top
Fungusfreakland
External


Since: Aug 30, 2006
Posts: 3



PostPosted: Wed Aug 30, 2006 4:55 pm    Post subject: Re: how do I increment a numbering system in excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi Dave,
All that comes up in cell E4 is a blank.
is there something I did wrong with the coding?

Thanks

Ken


"Fungusfreakland" wrote:

> Hi Norman,
> I am working with the VBA code that you've refered many people to and it
> kinda works.
> The date function works however the sequential numbering is not working.
> I've looked at the code over and over again and there are no errors according
> to the editor.
>
> I'm using Excel 2000 and trying to create my own invoice template.
>
> below is the code i'm using.
>
> Thank you
>
> Ken
>
>
> Sub INVOICE_NUMBER()
> Const sAPPLICATION As String = "Excel"
> Const sSECTION As String = "Invoice"
> Const sKEY As String = "Invoice_key"
> Const nDEFAULT As Long = 1&
> Dim nNumber As Long
>
> With ThisWorkbook.Sheets("Invoice")
> With .Range("E3")
> If IsEmpty(.Value) Then
> .Value = Date
> .NumberFormat = "dd mmm yyyy"
> End If
> End With
> With .Range("E4")
> If IsEmpty(.Value) Then
> nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY,
> nDEFAULT)
> .NumberFormat = "@"
> .Value = Format(nNumber, "0000")
> SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
> End If
> End With
> End With
>
>
>
> "Norman Jones" wrote:
>
> > Hi Allan,
> >
> > If you are unfamiliar with macros, see the notes by JE McGimpsey on another
> > page of his site:
> >
> > http://www.mcgimpsey.com/excel/index.html#usingvba
> >
> > Also see David McRitchie's 'Getting Started With Macros And User Defined
> > Functions at:
> >
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> > If you are still experiencing problems, post back.
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Allan" wrote in message
> >
> > > Hi Norman
> > >
> > > Sorry but I do not fully understand the macro.
> > >
> > > I have an Excel wookbook and in worksheet named "Sheet1" in cell I6 I wish
> > > to insert an new order number each time the workbook is opened that is the
> > > last PO number +1.
> > >
> > > I cannot see what to do with the link that you kindly sent to me.
> > >
> > > Can you help me further?
> > > Cheers
> > > Allan
> > >
> > >
> > > "Norman Jones" wrote:
> > >
> > >> Hi Allan,
> > >>
> > >> See J.E. McGimpsey's suggestions at:
> > >>
> > >>
> > >> http://www.mcgimpsey.com/excel/udfs/sequentialnums.html
> > >>
> > >> ---
> > >> Regards,
> > >> Norman
> > >>
> > >>
> > >>
> > >> "Allan" wrote in message
> > >>
> > >> >I wish to set up a Purchase Order system using Excel and i would like
> > >> >the
> > >> >PO
> > >> > number to automatically increment. Can anyone pleaseadvise me how to do
> > >> > this?
> > >>
> > >>
> > >>
> >
> >
> >
Back to top
Dave Peterson
External


Since: Jul 08, 2005
Posts: 16264



PostPosted: Wed Aug 30, 2006 6:12 pm    Post subject: Re: how do I increment a numbering system in excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

You may want to be more specific about what "not working" means.

Fungusfreakland wrote:
>
> Hi Norman,
> I am working with the VBA code that you've refered many people to and it
> kinda works.
> The date function works however the sequential numbering is not working.
> I've looked at the code over and over again and there are no errors according
> to the editor.
>
> I'm using Excel 2000 and trying to create my own invoice template.
>
> below is the code i'm using.
>
> Thank you
>
> Ken
>
> Sub INVOICE_NUMBER()
> Const sAPPLICATION As String = "Excel"
> Const sSECTION As String = "Invoice"
> Const sKEY As String = "Invoice_key"
> Const nDEFAULT As Long = 1&
> Dim nNumber As Long
>
> With ThisWorkbook.Sheets("Invoice")
> With .Range("E3")
> If IsEmpty(.Value) Then
> .Value = Date
> .NumberFormat = "dd mmm yyyy"
> End If
> End With
> With .Range("E4")
> If IsEmpty(.Value) Then
> nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY,
> nDEFAULT)
> .NumberFormat = "@"
> .Value = Format(nNumber, "0000")
> SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
> End If
> End With
> End With
>
> "Norman Jones" wrote:
>
> > Hi Allan,
> >
> > If you are unfamiliar with macros, see the notes by JE McGimpsey on another
> > page of his site:
> >
> > http://www.mcgimpsey.com/excel/index.html#usingvba
> >
> > Also see David McRitchie's 'Getting Started With Macros And User Defined
> > Functions at:
> >
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> > If you are still experiencing problems, post back.
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Allan" wrote in message
> >
> > > Hi Norman
> > >
> > > Sorry but I do not fully understand the macro.
> > >
> > > I have an Excel wookbook and in worksheet named "Sheet1" in cell I6 I wish
> > > to insert an new order number each time the workbook is opened that is the
> > > last PO number +1.
> > >
> > > I cannot see what to do with the link that you kindly sent to me.
> > >
> > > Can you help me further?
> > > Cheers
> > > Allan
> > >
> > >
> > > "Norman Jones" wrote:
> > >
> > >> Hi Allan,
> > >>
> > >> See J.E. McGimpsey's suggestions at:
> > >>
> > >>
> > >> http://www.mcgimpsey.com/excel/udfs/sequentialnums.html
> > >>
> > >> ---
> > >> Regards,
> > >> Norman
> > >>
> > >>
> > >>
> > >> "Allan" wrote in message
> > >>
> > >> >I wish to set up a Purchase Order system using Excel and i would like
> > >> >the
> > >> >PO
> > >> > number to automatically increment. Can anyone pleaseadvise me how to do
> > >> > this?
> > >>
> > >>
> > >>
> >
> >
> >

--

Dave Peterson
Back to top
Dave Peterson
External


Since: Jul 08, 2005
Posts: 16264



PostPosted: Wed Aug 30, 2006 9:48 pm    Post subject: Re: how do I increment a numbering system in excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

The code worked fine for me.

You are looking at E4 of the Invoice worksheet, right?

In JE's code, his procedure was named Workbook_Open (and that is located behind
the ThisWorkbook module).

That means that each time this workbook was opened, the code would run. If the
cell was empty (B1 in JE's code, E4 in yours), then that cell will get the value
of what's found in the registry plus 1.

Since you renamed the procedure (and I assume that you moved it to a general
module), how does your code get run?

Do you have a button on the worksheet that runs the code?

Fungusfreakland wrote:
>
> Hi Dave,
> All that comes up in cell E4 is a blank.
> is there something I did wrong with the coding?
>
> Thanks
>
> Ken
>
> "Fungusfreakland" wrote:
>
> > Hi Norman,
> > I am working with the VBA code that you've refered many people to and it
> > kinda works.
> > The date function works however the sequential numbering is not working.
> > I've looked at the code over and over again and there are no errors according
> > to the editor.
> >
> > I'm using Excel 2000 and trying to create my own invoice template.
> >
> > below is the code i'm using.
> >
> > Thank you
> >
> > Ken
> >
> >
> > Sub INVOICE_NUMBER()
> > Const sAPPLICATION As String = "Excel"
> > Const sSECTION As String = "Invoice"
> > Const sKEY As String = "Invoice_key"
> > Const nDEFAULT As Long = 1&
> > Dim nNumber As Long
> >
> > With ThisWorkbook.Sheets("Invoice")
> > With .Range("E3")
> > If IsEmpty(.Value) Then
> > .Value = Date
> > .NumberFormat = "dd mmm yyyy"
> > End If
> > End With
> > With .Range("E4")
> > If IsEmpty(.Value) Then
> > nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY,
> > nDEFAULT)
> > .NumberFormat = "@"
> > .Value = Format(nNumber, "0000")
> > SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
> > End If
> > End With
> > End With
> >
> >
> >
> > "Norman Jones" wrote:
> >
> > > Hi Allan,
> > >
> > > If you are unfamiliar with macros, see the notes by JE McGimpsey on another
> > > page of his site:
> > >
> > > http://www.mcgimpsey.com/excel/index.html#usingvba
> > >
> > > Also see David McRitchie's 'Getting Started With Macros And User Defined
> > > Functions at:
> > >
> > > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > >
> > > If you are still experiencing problems, post back.
> > >
> > >
> > > ---
> > > Regards,
> > > Norman
> > >
> > >
> > >
> > > "Allan" wrote in message
> > >
> > > > Hi Norman
> > > >
> > > > Sorry but I do not fully understand the macro.
> > > >
> > > > I have an Excel wookbook and in worksheet named "Sheet1" in cell I6 I wish
> > > > to insert an new order number each time the workbook is opened that is the
> > > > last PO number +1.
> > > >
> > > > I cannot see what to do with the link that you kindly sent to me.
> > > >
> > > > Can you help me further?
> > > > Cheers
> > > > Allan
> > > >
> > > >
> > > > "Norman Jones" wrote:
> > > >
> > > >> Hi Allan,
> > > >>
> > > >> See J.E. McGimpsey's suggestions at:
> > > >>
> > > >>
> > > >> http://www.mcgimpsey.com/excel/udfs/sequentialnums.html
> > > >>
> > > >> ---
> > > >> Regards,
> > > >> Norman
> > > >>
> > > >>
> > > >>
> > > >> "Allan" wrote in message
> > > >>
> > > >> >I wish to set up a Purchase Order system using Excel and i would like
> > > >> >the
> > > >> >PO
> > > >> > number to automatically increment. Can anyone pleaseadvise me how to do
> > > >> > this?
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >

--

Dave Peterson
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Excel General (archive) All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum