Numbering a worksheet

The following should work nicely . . .

Private Sub Workbook_Open()
Dim ref_number As Integer
ref_number = Range("A2").Value
ref_number = ref_number + 1
ActiveSheet.Unprotect Password:="XXXX"
Range("A2").Value = ref_number
ActiveSheet.Protect Password:="XXXX"
End Sub

NB - put this code in the ThisWorkbook object.

The Unprotect and Protect commands will prevent users overtyping the reference number (assuming the cell is set to Locked).

Hope this helps

JAF

I appreciate your answering my question. Would you be able to simplify this for me. I am new with Excel and some things are still very overwhelming for me. Thanks again for the help.

No problem

Open the worksheet on which you want the reference number to increase (I'm assuming that the number 1 (one) is in cell A2)).

Press Alt+F11 to open the Visual Basic Editor (VBE) or select Tools - Macro - Visual Basic Editor.

If the Project Explorer window is not active, press Ctrl+R (or select View - Project Explorer).

Find the name of your workbook in the Project Explorer window and click on the + sign to the left of it to expand the view.

Double click on the ThisWorkbook icon and paste the code into the window that appears.

Thank-you!!! Worked like a charm.

JAF

This can be reduced to :-

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

However, the problem with both procedures is that if the file is a template, the first time it is opened it will show Ref. No.1, and will show the same number every time it is opened.
Also, if the workbook opened from the template is saved as an .xls file, every time this .xls file is opened the Ref. No. will be increased by +1.
Even if the file is not a template, if it is saved as another file then the Ref. No. will change every time the other file is opened but the Ref. No. on the “master” file will not change.
This is the reverse of what should happen.
Any suggestions?

Celia

Well, Celia saw this coming and I haven't found a way to work around this one, but it is exactly as she said. Either the master sheet is always going back to the original # or changing when the xls is changed. Any ideas?

We have found a way to deal with this issue. When you open the main sheet (template) hit the "save" first, then do a "save as" and name and save the file to the correct place. This allows the master to be updated each time it is opened and still will not affect it when the "save as" copy is opened. I haven't had a problem with doing it this way so far. I hope it works for you as well.

JAF/Michaela

This is all very messy and I think the number on the “saved as” file still changes each time it is opened, which is presumably not what is required.

Can anyone supply some code that assigns numbers to workbooks in the same way as the MS Excel Invoice Template?

I use the following code(in a normal module) in some workbooks to assign a consecutive number each time a template is opened as a workbook, but I don’t really like it.
It seems to me to be a bit “indirect” (I wrote it when first starting to learn VBA about three months ago and haven’t got round to working out a better way).

Although it works, would like something more “streamlined” if possible.

‘Copy the ref. no. on the active worksheet
Range("A1").Copy
'Open the template
Workbooks.Open filename:="C:Myfile.xlt", Editable:=True
‘Paste to the template the copied ref. no. + 1
With Range("A1")
.Value = "1"
.PasteSpecial Paste:=xlValues, Operation:=xlAdd
End With
Application.CutCopyMode = False
'Save and close the template
ActiveWorkbook.Close savechanges = True

Celia