Automatic auto-numbering a form


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Automatic auto-numbering a form

  1. #1
    Join Date
    Aug 2004
    Posts
    43,023

    Automatic auto-numbering a form

    [Originally posted by Al Halowell]

    Hello,

    I found a snippet of code to automatically number a form I designed. It works well, but I have 2 issues:

    1. I need it to run as soon as the template is opened, no pushing buttons or anything.

    2. Instead of putting the value in a message box, I need the cell (M1) to have the value of
    ThisInvoice.

    The macro is shown under my name - below.

    I have little experience with macros. Any/all help would be greatly appreciated. Please email suggestions.

    Thank you all for your patience and assistance!

    Regards,
    Al

    Sub NewInvoiceNumber()
    Dim ThisInvoice As Long
    Dim ReadText As String
    Dim StoreFile As String

    StoreFile = "C:\TEMP\Number.num"
    'read previous number:
    If Dir(StoreFile) = "" Then 'not found
    ThisInvoice = 1
    Else
    Open StoreFile For _
    Input Access Read As #1
    While Not EOF(1)
    Line Input #1, ReadText
    ThisInvoice = Val(ReadText)
    Wend
    Close #1
    End If
    ThisInvoice = ThisInvoice + 1
    MsgBox "Requisition # " & ThisInvoice
    'replace previous with "paste into sheet code
    'Store this number:
    Open StoreFile For _
    Output Access Write As #1
    Print #1, ThisInvoice
    Close #1
    End Sub

    Private Sub Worksheet_Activate()

    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    End Sub


    Private Sub Workbook_Open()

    End Sub

  2. #2
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Automatic auto-numbering a form

    [Originally posted by Andon K.]

    First, instead of a Sub, make NewInvoiceNumber a Function that returns the new invoice number:

    Function NewInvoiceNumber() As Long
    Dim ThisInvoice As Long
    Dim ReadText As String
    Dim StoreFile As String

    StoreFile = "C:\TEMP\Number.num"
    'read previous number:
    If Dir(StoreFile) = "" Then 'not found
    ThisInvoice = 1
    Else
    Open StoreFile For _
    Input Access Read As #1
    While Not EOF(1)
    Line Input #1, ReadText
    ThisInvoice = CLng(ReadText)
    Wend
    Close #1
    End If

    ThisInvoice = ThisInvoice + 1
    NewInvoiceNumber = ThisInvoice

    'replace previous with "paste into sheet code
    'Store this number:
    Open StoreFile For _
    Output Access Write As #1
    Print #1, ThisInvoice
    Close #1
    End Sub

    I am not sure what do you mean exactly by "as soon as the template is opened", but if it means that you want to generate a new invoice number on openinig the workbook, this should do the job:

    Private Sub Workbook_Open()
    ActiveWorkbook.Worksheets("YourSheetName").Range("M1").Value = NewInvoiceNumber()
    End Sub

    HTH

  3. #3
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Automatic auto-numbering a form

    [Originally posted by Al Halowell]

    Anton,

    Thank you! I will try your suggestion today.

    Al

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center