Click here for important information about

Unique number for forms.

Microsoft Excel 2007
January 8, 2010 at 09:08:29
Specs: Windows XP
I have a master maintenance request form that will live on a server. I would like it to open with a new sequential number as a new document every time a user clicks on it. Is there anyway to do this in excel?

See More: Unique number for forms.

January 8, 2010 at 09:15:25
Sorry. I am using Excel 2007.

Report •

January 8, 2010 at 11:02:56

This code in the Open event of your master workbook will create a new copy of the master workbook with a name that includes a sequential number, that updates each time the master is opened. This example creates FormA0001.xls, FormA0002.xls etc.

In your master workbook, click Alt + f11 (The Alt key and function key 11).
In the visual basic window that opens, look in the Project explorer (usually on the left) - if not visible select View- Project explorer from the VB Menu. Find the name of your master workbook - after VBAProject e.g., VBAProject(Master.xls)
In the Microsoft Excel Object double click 'ThisWorkbook'.
In the code window on the right enter this code:

Option Explicit

Private Sub Workbook_Open()
Dim intSequence As Integer
Dim strStem As String
Dim strType As String

'stem of filename
strStem = "FormA"
'get filetype
strType = Right(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - _
    InStr(1, ActiveWorkbook.Name, ".") + 1)
'get last sequence number
intSequence = Worksheets("Sheet2").Range("A1").Value
'save next sequence number
Worksheets("Sheet2").Range("A1").Value = intSequence + 1
'Save this Master
'save as a new Workbook
Me.SaveAs Filename:=strStem & Format(intSequence, "000#") & strType
End Sub

Note that the line of code starting strType has been split over two lines using the line continuation character '_'
It can be copied and pasted 'as is' or the '_' can be removed and brought back to a single line.

Save from the VB file menu

Use Alt+f11 to go back to the main Excel workbook.
Goto Sheet2 and enter the number 0 in cell A1.
Save the workbook and close it.

Open the workbook and it will update the number in cell A1 of Sheet2 and then save itself, then save As with a new filename - in this example FormA0001.xls

The new Workbook is saved in the default Workbook format and with the same extension as the Master.The user now has a unique named workbook and it is their responsibility to save it.

Hope this was what you were looking for, or at least gives you an idea for how to do what you wanted.


Report •
Related Solutions

Ask Question