A
Andy
An invoice is created from an xlt template, details completed, and then
saved as an xls "invoice" file.
I found the following code with Google, and am running it from a button to
create an invoice auto number sequence.
Public Function NextSeqNumber(Optional sFileName As String, Optional
nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "Z:\Path..."
Const sDEFAULT_FNAME As String = "maccwktpnum.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 num()
ThisWorkbook.Sheets(1).Range("I12").Value = NextSeqNumber
End Sub
All is well with this, and it does the job. However I have identified a
operational weakness in that a new number is incremented every time the
button is pressed......As it should do.....but operationally a nightmare.
The macro is also resident and available in the saved xls invoice, so again
risks are present when the invoice is viewed.
How can I modify the above to run once only, when the invoice template is
used, or even better erase / disable the button or macro after it has been
clicked once.
I did find..
Private Sub CommandButton1_Click()
Worksheets("sheet1").Range("a1:a1").Value = "Hello World"
Worksheets("sheet1").CommandButton1.Enabled = False
End Sub
from a Google, but am unsure how to incorporate it!!!!
Ideally if the macro can be erased then will the completed invoice, saved as
an xls, not have the enable macro prompt each time the file is opened to
view?
TIA
Andy
saved as an xls "invoice" file.
I found the following code with Google, and am running it from a button to
create an invoice auto number sequence.
Public Function NextSeqNumber(Optional sFileName As String, Optional
nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "Z:\Path..."
Const sDEFAULT_FNAME As String = "maccwktpnum.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 num()
ThisWorkbook.Sheets(1).Range("I12").Value = NextSeqNumber
End Sub
All is well with this, and it does the job. However I have identified a
operational weakness in that a new number is incremented every time the
button is pressed......As it should do.....but operationally a nightmare.
The macro is also resident and available in the saved xls invoice, so again
risks are present when the invoice is viewed.
How can I modify the above to run once only, when the invoice template is
used, or even better erase / disable the button or macro after it has been
clicked once.
I did find..
Private Sub CommandButton1_Click()
Worksheets("sheet1").Range("a1:a1").Value = "Hello World"
Worksheets("sheet1").CommandButton1.Enabled = False
End Sub
from a Google, but am unsure how to incorporate it!!!!
Ideally if the macro can be erased then will the completed invoice, saved as
an xls, not have the enable macro prompt each time the file is opened to
view?
TIA
Andy