Want to create a form in Excel that will generate-

  oo7juk 12:02 29 Aug 2005

A new number every time I close the page.

e.g input the data and the entry number is 0001, when I save the data a close down, then re-open a new form appears with entry number 0002.

Hope I'm making sense.

Many thanks.

  VoG II 12:29 29 Aug 2005

OPen Excel. ALT + F11 to open the Visual Basic Editor. Insert | Module. Paste in

Sub Auto_Open()

Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Range("A1").Value + 1

End Sub

Change "Sheet1" and "A1" to suit.

Close the visual Basic Editor and save the workbook.

  pj123 15:25 29 Aug 2005

Also had the same problem with my Invoices and Order numbers. I found that Excel cannot cope with "circular references" (whereas, when I was using Lotus 123 it could).

Haven't tried VoG™ solution but I have written a macro to cover it. It doesn't change automatically when opening the file, but that is probably because I haven't set it up to do that. It also requires a .txt file to be stored on the hard disk somewhere with the latest number in it.

It is:

Sub Invnumber()
On Error Resume Next
Open "f:\invnumbers\invoice.txt" For Input As #1
Input #1, currentnumber
Close #1
nextnumber = currentnumber + 1
ActiveCell.FormulaR1C1 = nextnumber
Open "f:\invnumbers\invoice.txt" For Output As #1
Write #1, nextnumber
Close #1

End Sub

You will need to change some values and names to suit your worksheet.

I have assigned a shortcut key as Ctrl + i which updates it as and when I need it.

I am sure that the solution that VoG™ has supplied is better than mine.

  oo7juk 12:38 30 Aug 2005


After I open excel and select insert module, another window appears, do I paste anything into that?

  VoG II 12:51 30 Aug 2005

A new module should open as a plain white 'sheet'. Paste the code into that.

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

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

Photoshop CC 2018 released with new Curvature Pen and better brush tools

Camera tips to take better iPhone photos

Les meilleures applications de covoiturage 2017