Autonumber Newbie Assistance... Please

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

Options, from easy to not as easy:
(I will assume the button, CommandButton1, is on Sheet1):


' DISABLE BUTTON AFTER CODE IS RUN:
Private Sub CommandButton1_Click()

' YOUR CODE
Sheets("Sheet1").Shapes("CommandButton1").ControlFormat.Enabled = False

End Sub

DELETE BUTTON AFTER CODE IS RUN:
Private Sub CommandButton1_Click()

' YOUR CODE
Sheets("Sheet1").Shapes("CommandButton1").Delete

End Sub

Finally, to remove all traces of the button AND code (assuming all code is
in a separate module called Module1 - and (important) you have added a
reference in the project for Microsoft Visual Basic for Applications
Extensibility (VBIDE)):

Private Sub CommandButton1_Click()
Dim ButtonModule as VBComponent

' YOUR CODE

' Get rid of the button:
Sheets("Sheet1").Shapes("CommandButton1").Delete
' Deleted after your code has run

' Next section will get rid of the module:
With ThisWorkbook.VBProject
Set ButtonModule = .VBComponents("Module1")
.VBComponents.Remove ButtonModule
End With

End Sub

HTH!

"Andy" wrote:

> 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
>
>
>
>

Thanks

I'll need a day or so to get my head around your suggestions !!!

But I will try them.

Rgds
Andy


"K Dales" <[email protected]> wrote in message
news:[email protected]...
> Options, from easy to not as easy:
> (I will assume the button, CommandButton1, is on Sheet1):
>
>
> ' DISABLE BUTTON AFTER CODE IS RUN:
> Private Sub CommandButton1_Click()
>
> ' YOUR CODE
> Sheets("Sheet1").Shapes("CommandButton1").ControlFormat.Enabled = False
>
> End Sub
>
> DELETE BUTTON AFTER CODE IS RUN:
> Private Sub CommandButton1_Click()
>
> ' YOUR CODE
> Sheets("Sheet1").Shapes("CommandButton1").Delete
>
> End Sub
>
> Finally, to remove all traces of the button AND code (assuming all code is
> in a separate module called Module1 - and (important) you have added a
> reference in the project for Microsoft Visual Basic for Applications
> Extensibility (VBIDE)):
>
> Private Sub CommandButton1_Click()
> Dim ButtonModule as VBComponent
>
> ' YOUR CODE
>
> ' Get rid of the button:
> Sheets("Sheet1").Shapes("CommandButton1").Delete
> ' Deleted after your code has run
>
> ' Next section will get rid of the module:
> With ThisWorkbook.VBProject
> Set ButtonModule = .VBComponents("Module1")
> .VBComponents.Remove ButtonModule
> End With
>
> End Sub
>
> HTH!
>
> "Andy" wrote:
>
>> 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
>>
>>
>>
>>