Excel - auto increment data on opening

  cedricthecat 14:39 22 Jan 2007

A colleague has just mentioned a problem with a form they use.

Basically they want a number to increment by one each time they open the form.

I've Googled and found this as what looks like a solution, but can't see how to make it work!

"In the Workbook module (Or, if it’s a Userform, in the Initialize event of that Userform) put the following:

Private Sub Workbook_Open()
Sheet1.Range("A1") = Sheet1.Range("A1") + 1
End Sub"


  pj123 14:51 22 Jan 2007

I have two Macro's that increment an Invoice Nbr and and Order Nbr, but they are not automatic.

I use Ctrl + o for the order number and Ctrl + i for the invoice number.

Don't know whether either of these can be made to work automatically but if you would like to try them I can email them to you.

Contact me via the envelope if you want to try them.

  cedricthecat 14:54 22 Jan 2007

Managed to get this to run as a macro from a button, as

Sub IncrementNumber()
Sheet1.Range("A1") = Sheet1.Range("A1") + 1
End Sub

Just need to figure out how to automate it now!

  VoG II 15:03 22 Jan 2007

Private Sub Workbook_Open()
Sheet1.Range("A1").Value = Sheet1.Range("A1").Value + 1
End Sub

Right click the Excel logo just to the left of File, View Code, paste it in.

  cedricthecat 15:09 22 Jan 2007

Thanks Vog - I'm actually starting to very slowly learn here and had literally just worked this out!

  Chris the Ancient 16:47 22 Jan 2007

Welcome to the wonderful (and mysterious) world of Excel macros.

You've been bitten!

However, there is always a wealth of specialist info on this topic in this forum - usually thanks to VoG™ - and you'll never be sane again!

I started as a 'dabbler' - but VoG™ had made me a lot more professional about it.

  cedricthecat 16:54 22 Jan 2007


I don't know how he does it!

  cedricthecat 09:18 23 Jan 2007

Thanks for the help with this yesterday folks - but now got another element thrown into the mix! (Always the same - someone asks for help, you come up with a solution, then they want something different!)

What they are asking for now, is to increment the code number on each fresh print ie, Start at 1, print, increment to 2, print, increment to 3 etc.

They are talking about printing batches of "50 or so", so I'd guess they'll also need something to control how many are done at a time

Sorry to throw this one back out!

  VoG II 09:32 23 Jan 2007

In the workbook's code module:

Private Sub Workbook_BeforePrint(cancel As Boolean)
Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Range("A1").Value + 1
End Sub

However, if they use File > Print and print 50 copies this will only increment A1 by 1.

  cedricthecat 09:44 23 Jan 2007

Can't get that to work!

Each time I print, the number resolutely stays at 1.....!

  VoG II 09:49 23 Jan 2007

You did put it in the workbook's code module? Right click the Excel logo > View Code.

By the way, to save destroying a rain forest whilst testing, Print Preview should also trigger the code. I don't know of a way of getting this event handler to distinguish between Print and Print Preview :o(

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iMac Pro review

Illustrator Charles Williams on how to create magazines and book covers

iMac Pro review

Les meilleures prises CPL (2018)