Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

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

  1. #11
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,655
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Auto generation of serial number in user form

    If Sheet2 is not active at running that macro then I'd suggest add .Worksheet. into this code line:
    x = .Worksheet.Evaluate(...

    And for that code it is assumed the sheet name is "Sheet2" and the serial numbers are in its A2 and the below cells, in which cells actually the serial numbers are?
    Last edited by ZVI; Oct 20th, 2019 at 04:18 PM.
    Vladimir Zakharov

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

    Default Re: Auto generation of serial number in user form

    This is my code

    Private Sub UserForm_Initialize()
    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")
    End Sub

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

    Default Re: Auto generation of serial number in user form

    Whilst ZVI has made a vary good point about making sure that the code is looking at the correct sheet, the only way I can get a "Application-defined or object-defined error." error is if there is no textbox on the userform that is called "TextBox1".

    If you add a new line before "End Sub" and type in Me.T you should get a list of all the controls on the userform that begin with T, is Textbox1 listed?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #14
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,655
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Auto generation of serial number in user form

    May be try also a bit modified code where "Sheet2" is replaced with 2 and the Evaluate formula ignore empty and not serial number values:
    Code:
    Sub test()
        Dim x As Long
        With Sheets(2).Range("A2", Sheets(2).Range("A" & Rows.Count).End(xlUp))
            x = .Worksheet.Evaluate("MAX(IF(ISNUMBER(--RIGHT(" & .Address & ",3)),--RIGHT(" & .Address & ",3)))")
        End With
        TextBox1 = "TARA-SS-" & Format(x + 1, "000")
        'Debug.Print TextBox1
    End Sub
    But as Fluff has already marked it is not for Application-defined or object-defined error
    Last edited by ZVI; Oct 20th, 2019 at 04:52 PM.
    Vladimir Zakharov

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

    Default Re: Auto generation of serial number in user form

    I added .Worksheet to that line in the code and change A2 to A4 which is the cell where th serial number starts. A4 has written TARA-SS-0001. As for "Sheet2" I changed it for "Database" as this is the name of the sheet where the serial numbers are, but the code is still not working, it is showing me the same exact error.

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

    Default Re: Auto generation of serial number in user form

    Hello,

    Thank you very much fr your help on this. I follow your instructions and it is still giving me the same error. I checked and TextBox1 exist.

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

    Default Re: Auto generation of serial number in user form

    It is working now! You might laughed at me due to the silly mistake I had (x1Up) instead of (xlUp).

    Also, what is the difference between the first code and the second one (this below). The second one will not look for empty cells?
    Sub test()
    Dim x As Long
    With Sheets(2).Range("A2", Sheets(2).Range("A" & Rows.Count).End(xlUp))
    x = .Worksheet.Evaluate("MAX(IF(ISNUMBER(--RIGHT(" & .Address & ",3)),--RIGHT(" & .Address & ",3)))")
    End With
    TextBox1 = "TARA-SS-" & Format(x + 1, "000")
    'Debug.Print TextBox1
    End Sub

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

    Default Re: Auto generation of serial number in user form

    That's right, the code I supplied will fail if there are blank cells in the range, whilst ZVI's code won't.
    Also ZVI's code will ignore any cells where the the last 3 digits are non-numeric.
    - 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. #19
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,655
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Auto generation of serial number in user form

    According to the post 15 it seems that there are 4 digits in the serial number "A4 has written TARA-SS-0001", if so then replace 3 by 4 in the code. and use "0000" in the result formatting. As to the code - the initial Fluf's version is faster and applicable for the posted data.
    Vladimir Zakharov

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

    Default Re: Auto generation of serial number in user form

    Thank you both for your help and patience, the codes provided work perfectly.

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
  •