+ Reply to Thread
Results 1 to 8 of 8

Userform creates concatenated number sequence - needs padded zeros

  1. #1
    Registered User
    Join Date
    07-04-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    36

    Userform creates concatenated number sequence - needs padded zeros

    I have a userform that has two comboboxes get populated with numbers from 1 to 32

    Please Login or Register  to view this content.
    User can pick a number in each dropdown, and then when they press OK I create a concatenated sequence

    16-16 using this:

    Please Login or Register  to view this content.
    This works fine until the user picks anything between 1 and 9, because the number sequence is now 1-2 or something, which is 3 digits long instead of the intended 5.

    I know there is a way to format this, I have been searching for ways, one is to do this as text, other is use format when populating the combobox. I can't seem to find a good answer as to how to best get the padded zeros into my code. Any help would be appreciated.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,624

    Re: Userform creates concatenated number sequence - needs padded zeros

    How about
    Cells(emptyRow, 11).Value = format(ProductCode1.Value,"00) & "-" & Format(ProductCode2.Value,"00")

  3. #3
    Registered User
    Join Date
    07-04-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Userform creates concatenated number sequence - needs padded zeros

    Quote Originally Posted by Fluff13 View Post
    How about
    Cells(emptyRow, 11).Value = format(ProductCode1.Value,"00) & "-" & Format(ProductCode2.Value,"00")
    That looks like it will work, but if I try it I end up with the same result. I thought I could change it to text during the format like this:

    Cells(emptyRow, 11).Value = format(ProductCode1.text,"00") & "-" & Format(ProductCode2.text,"00")

    But this didn't work either.

  4. #4
    Registered User
    Join Date
    07-04-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Userform creates concatenated number sequence - needs padded zeros

    Quote Originally Posted by Fluff13 View Post
    How about
    Cells(emptyRow, 11).Value = format(ProductCode1.Value,"00) & "-" & Format(ProductCode2.Value,"00")
    I'm a fool! I was looking at the wrong result, I moved it to test it and I was getting no change because of that. I changed it back to input in the right place and behold, it worked. Thank you!

  5. #5
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Userform creates concatenated number sequence - needs padded zeros

    Value in A1 is text '01-02

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 04-20-2020 at 12:49 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,759

    Re: Userform creates concatenated number sequence - needs padded zeros

    Hi there,

    See if using the following line of code does what you need:

    Please Login or Register  to view this content.

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  7. #7
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,049

    Re: Userform creates concatenated number sequence - needs padded zeros

    Quote Originally Posted by Fluff13 View Post
    How about
    Cells(emptyRow, 11).Value = format(ProductCode1.Value,"00") & "-" & Format(ProductCode2.Value,"00")
    It looks like the OP caught it, but just pointing out you omitted a quote mark (shown in red above).

    Here is another way to write this line of code where I use only one Format function call...

    Cells(emptyRow, 11).Value = Format(100 * ProductCode1.Value + ProductCode2.Value, "00-00")

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,624

    Re: Userform creates concatenated number sequence - needs padded zeros

    it worked. Thank you!
    You're welcome & thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sequence of numbers (separated by zeros) with highest SUM
    By Daniel88 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 12-18-2016, 10:08 AM
  2. Replies: 7
    Last Post: 06-08-2015, 08:39 AM
  3. UserForm that creates a new sheet as needed
    By rscarbo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-12-2014, 06:36 PM
  4. Userform textbox value with formula creates a result in excel
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-02-2013, 05:21 PM
  5. Checkboxes in Userform Creates Multiple Data Entries
    By Daneshav in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-01-2013, 09:37 PM
  6. [SOLVED] Generating 5 ones or zeros in a row, in any sequence with 3-2 proportion
    By szasz in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-21-2013, 05:17 AM
  7. Getting month padded zero
    By kickyr in forum Excel General
    Replies: 3
    Last Post: 02-20-2006, 11:10 AM

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