I'm having a problem with some Sequential Numbering code I found 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 and 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 site regarding Excel Sequential Numbers to handle the sequential number part in cell C2.

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

The code follows:
Public Function NextSeqNumber(Optional sFileName As String, Optional 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 Sub