Autonumber Newbie Assistance... Please

[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

 

[K Dales]

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]

Thanks

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

But I will try them.

Rgds
Andy