Sequential Numbering Help

[BadgerFanToo]

I'm having a problem with some Sequential Numbering code I foun
searching this site, can anyone please help me resolve this?

Here is some background:
I have a resulting cell, E6, which should have a value of YYDDD-nnnnn.

YYDDD is the julian date which I have formulated in a hidden cell B2.

nnnnn is a sequential number which I am retrieving from a .txt file an
storing in a hidden cell C2.

I then concatenate B2&"-"&C2, and display in cell E6.

E6 is a locked cell and my sheet is protected.

I'm using borrowed code from McGimpsey and Associates web sit
regarding Excel Sequential Numbers to handle the sequential number par
in cell C2.

My problem is...this does NOT work automatically when a new spreadshee
is created from my template. I have to go to debug and run the macr
manually. I need to fetch the next sequential number automatically whe
a new sheet is created, can anyone please help me with this?

The code follows:
Public Function NextSeqNumber(Optional sFileName As String, Optiona
nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "Network Drive Path Goes Here"
Const sDEFAULT_FNAME As String = "projectid.txt"
Dim nFileNumber As Long

nFileNumber = FreeFile
If sFileName = "" Then sFileName = sDEFAULT_FNAME
If InStr(sFileName, Application.PathSeparator) = 0 Then _
sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName
If nSeqNumber = -1& Then
If Dir(sFileName) <> "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber
nSeqNumber = nSeqNumber + 1&
Close nFileNumber
Else
nSeqNumber = 1&
End If
End If
On Error GoTo PathError
Open sFileName For Output As nFileNumber
On Error GoTo 0
Print #nFileNumber, nSeqNumber
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1&
End Function
**************************************************
Public Sub Workbook_Open()
ThisWorkbook.Sheets(1).Range("c2").Value = NextSeqNumber
End Su

 

[JE McGimpsey]

One way:

Modify the code that follows that macro on that web page:

Public Sub Workbook_Open()
With ThisWorkbook.Sheets(1).Range("C2")
If IsEmpty(.Value) Then .Value = NextSeqNumber
End With
End Sub


BadgerFanToo

 

[BadgerFanToo]

I'm sorry, but the code modification you gave does not solve th
problem. Even if cell C2 is empty, the procedure does NOT ru
auto-magically when someone is creating a new spreadsheet using th
template (.xlt). The only way I can get this to work is to use debu
and step through the code to see if everything is working. If C2 i
empty, then your code modification works, but NOT automatically.

What I need is when someone starts Excel, goes to File>New>Templates O
My Computer, and creates a new spreadsheet using the template, I need t
get the spreadsheet to open with the next sequential numbe
automatically, so I can build my Project ID as stated in my earlie
post.

Is it possible that I have the code in the wrong place? I am NOT a V
programmer or Excel wizard.

Can someone please point me in the right direction.

Thanks, in advance, for any and all help

 

[BadgerFanToo]

Additional environment info: I'm trying to accomplish this using Exce
2003 SP1 and Win XP professional at the desktop level.

I've had a look at McGimpsey's "where to put your code," but I don'
get it. Can anyone help

 

[JE McGimpsey]

The code needs to be in your ThisWorkbook code module.

BadgerFanToo

 

[BadgerFanToo]

How do I do that? :confused

 

[BadgerFanToo]

Hallelujah, it works as designed. Once I figured out how to put the
code where it's supposed to go, it worked like a charm.

Thanks to all for the help.

Cheers!

 

[JE McGimpsey]

This link is on the page you took the original code from:

http://www.mcgimpsey.com/excel/modules.html


BadgerFanToo