auto-generate a number in a cell on opening new workbook

[Robb27]

I have a template workbook that is used for student progress reports. Each
workbook contains several sheets (progress reports, one for every class). Is
there a way, when I start a new workbook from the template for a new student
that it can autogenerate a new student number in a cell on the main worksheet
(overall transcript) that I can use to identify each student with?

Thanks,

Rob

 

[Frank Kabel]

Hi
one way (using the Windows registry for storing the last number). Put
the following code in the workbook module (not in a standard module) of
your template:
- It changes cell A1 of the first sheet
- you may change the key identifiert according to your needs (e.g.
DEFAULTSTART, MYLOCATION, etc.)

Private Sub Workbook_Open()
Const DEFAULTSTART As Integer = 1
Const MYAPPLICATION As String = "Excel"
Const MYSECTION As String = "myInvoice"
Const MYKEY As String = "myInvoiceKey"
Const MYLOCATION As String = "A1"
Dim regValue As Long

With ThisWorkbook.Sheets(1).Range(MYLOCATION)
If .Text <> "" Then Exit Sub
regValue = GetSetting(MYAPPLICATION, MYSECTION, _
MYKEY, DEFAULTSTART)
.Value = CStr(regValue)
SaveSetting MYAPPLICATION, MYSECTION, MYKEY, regValue + 1
End With
End Sub

 

[Robb27]

Thanks for your help Frank. I pasted that code into where I thought it should
go, but I must have done something wrong...I couldn't get it to work. Can you
explain it in a little more basic terms for me?

Thanks,

Rob

 

[Gord Dibben]

Robb

Don't see Frank around today so I'll take a crack at this.

The code would be pasted into the ThisWorkbook module.

With your Template open hit ALT + F11 to get to Visual Basic Ediotr.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and hit the + to expand.

Expand "Microsoft Excel Objects"

Double-click on ThisWorkbook to open the module.

Paste in there.

Save the file, close and re-open.

Gord Dibben Excel MVP