Results 1 to 7 of 7

Thread: Excel VBA Form: Add an Incrementing Serial Number
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2014
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel VBA Form: Add an Incrementing Serial Number

    Hi there,
    I have an Excel VBA form that populates a sheet. The problem that I could do with some help on is that I would like to be able to have a text box that adds an incrementing serial number. I can't figure out how to add the code that can look at the last completed row in the sheet, then return the value of the last serial number then add 1. If this is possible I would really appreciate some help!

    Many thanks

  2. #2
    Board Regular
    Join Date
    Jul 2002
    Location
    Derbyshire, UK
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Form: Add an Incrementing Serial Number

    Assumming serial numbers are numeric and in Column A and the data starts in A1. Sheet is Sheet1

    Code:
     Dim rData As Range
    
    
        Set rData = Sheet1.Range("A1").CurrentRegion
       
        rData.Rows.Count.Offset(1).Value = Application.WorksheetFunction.Max(rData.Columns(1)) + 1
    Hope that helps.

    Free Excel sample DataBase Form here

    royUk's web site

    "making Excel work for you"

  3. #3
    New Member
    Join Date
    Jul 2014
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Form: Add an Incrementing Serial Number

    Hi Roy,
    thank you for replying.how do I relate the code to a text box on my form so that it adds in the serial number with the rest of the form data?

  4. #4
    Board Regular
    Join Date
    Jul 2002
    Location
    Derbyshire, UK
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Form: Add an Incrementing Serial Number

    You don't really need a TextBox for the ID but I would have a TextBox on the Form & when you add data or load the Form place the ID in it using the code

    Code:
    Option Explicit
    
    
    Dim rData As Range
    
    
    Private Sub CommandButton1_Click()
        Cells(rData.Rows.Count + 1, 1).Value = Me.TextBox1.Value
        Set rData = Sheet1.Range("A1").CurrentRegion
        Me.TextBox1.Value = Format(WorksheetFunction.Max(rData.Columns(1)) + 1, "0000")
    End Sub
    
    
    Private Sub UserForm_Initialize()
        Set rData = Sheet1.Range("A1").CurrentRegion
        Me.TextBox1.Value = Format(WorksheetFunction.Max(rData.Columns(1)) + 1, "0000")
    End Sub
    Hope that helps.

    Free Excel sample DataBase Form here

    royUk's web site

    "making Excel work for you"

  5. #5
    New Member
    Join Date
    Jul 2014
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Form: Add an Incrementing Serial Number

    you're a genius. works superbly.
    thank you very much, seriously appreciated.

  6. #6
    Board Regular
    Join Date
    Jul 2002
    Location
    Derbyshire, UK
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Form: Add an Incrementing Serial Number

    Glad it helped. You should apply the same method of declaring the data range to apply the other TextBoxes, see my DatbaseForm example.
    Hope that helps.

    Free Excel sample DataBase Form here

    royUk's web site

    "making Excel work for you"

  7. #7
    New Member
    Join Date
    Jul 2014
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Form: Add an Incrementing Serial Number

    Thanks Roy.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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