+ Reply to Thread
Results 1 to 3 of 3

Autonumber Newbie Assistance... Please

  1. #1
    Andy
    Guest

    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




  2. #2
    K Dales
    Guest

    RE: Autonumber Newbie Assistance... Please

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


  3. #3
    Andy
    Guest

    Re: Autonumber Newbie Assistance... Please

    Thanks

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

    But I will try them.

    Rgds
    Andy


    "K Dales" <KDales@discussions.microsoft.com> wrote in message
    news:AC063D51-FF09-4146-BFA7-01ECD3F9B4CB@microsoft.com...
    > 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
    >>
    >>
    >>
    >>




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1