Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Auto generation of serial number in user form
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Auto generation of serial number in user form

    I have a database in sheet 2 with certain serial numbers and a userform in sheet 1 that allows me to manage this database, including adding new entries.Each entry needs to have a unique serial number. The serial number can not be duplicated.The serial number follows this format TARA-SS-001. I need the last three digits to increment by one based on my last serial number in my database in sheet 2. For example, if my last serial number is TARA-SS-001 in my data base my next serial number should be TARA-SS-002. Upon opening the userform, the serial number should appear on Textbox1 prefilled. I just cannot come with any code that can help me on this, I am pretty new to VBA. Can someone pease help me to find a code?

    Thank you very much.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Auto generation of serial number in user form

    Hi & welcome to MrExcel.
    How about
    Code:
        Dim x As Long
        With Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp))
            x = Evaluate("max(--right(" & .Address & ",3))")
        End With
        TextBox1.Value = "TARA-SS-" & Format(x + 1, "000")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Oct 2019
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto generation of serial number in user form

    Hi! Thank you very much for your quick reply on this. I really appreciate it and thank you for welcoming to this amazing forum.

    I tried to run the code but it not working. I have double checked it and everything is the same as the code you kindly provided me with. What else can I do? The SS on the serial number can be change fro the last digit of the current year we are if that makes a difference.

    Again, thank you very much!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Auto generation of serial number in user form

    In what way doesn't it work?
    Do you get any error messages?
    Do you get the wrong value?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Oct 2019
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto generation of serial number in user form

    Sorry for not being more specific.

    It gives me error "1004"
    Application-defined or object-defined error.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Auto generation of serial number in user form

    You need to change the name of the textbox to match the name of your textbox.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Oct 2019
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto generation of serial number in user form

    My textbox is the same. TextBox1, but it is not working and it still shows me the same error.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Auto generation of serial number in user form

    Did you put the code in the userform initialize event?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Oct 2019
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto generation of serial number in user form

    Yes I did, from the beginning, but still showing the same error.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Auto generation of serial number in user form

    Can you post your current code?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •