+ Reply to Thread
Results 1 to 11 of 11

Dynamic Combo Box list length with 2 columns

  1. #1
    Forum Contributor
    Join Date
    06-12-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    167

    Dynamic Combo Box list length with 2 columns

    Hello,

    I have a combo box that I am trying to load a list into. The problem is the list can be anywhere from 1 to 20 entries long and it has 2 columns. I currently can get the list to fill but it always has 20 entries so anything less than 20 followed by blank lines that aren't needed. Here is what I have:

    Please Login or Register  to view this content.
    The variable stages determines how many rows are needed

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Dynamic Combo Box list length with 2 columns

    I have not tested this., but would it work to count the rows before you dimmed cb?

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    06-12-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    167

    Re: Dynamic Combo Box list length with 2 columns

    I tried that by moving the dim after stages is defined and setting the length to be equal to that variable. I got the error message saying it must be a constant.

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Dynamic Combo Box list length with 2 columns

    davesexcel's suggestion seems the more efficient way (though perhaps instead of the last row, the idea is to use the value of stages as your upper boundary for the dim. That said, you could always loop through the combobox afterwards and remove any blank items if the first idea doesn't work.

    Also, if the array only gets 2 values, why are you dimming the second dimension to 20 instead of 2?

    Please Login or Register  to view this content.
    should maybe be:

    Please Login or Register  to view this content.
    That would mean up to 20 rows but only 2 wide? of course the 20 could be dimmed using stages, to avoid empty "rows".
    Last edited by Arkadi; 01-03-2018 at 03:08 PM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  5. #5
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Dynamic Combo Box list length with 2 columns

    Attaching a sample workbook enables others to work on your problem:

    To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and click "manage attachments" to open the upload window.


    To add a file to a post

  6. #6
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Dynamic Combo Box list length with 2 columns

    Try using redim:

    ReDim cb(1 To stages, 1 To 2) As String

  7. #7
    Forum Contributor
    Join Date
    06-12-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    167

    Re: Dynamic Combo Box list length with 2 columns

    Here's the sample
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    06-12-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    167

    Re: Dynamic Combo Box list length with 2 columns

    Arkadi the reDim worked perfect thanks. What exactly does the reDim do as this is new syntax for me.

  9. #9
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Dynamic Combo Box list length with 2 columns

    ReDim re-dimensions a variable, in the case of an array usually to a new size. Be aware that re-dim will erase the current contents of a variable unless you use ReDim Preserve

  10. #10
    Forum Contributor
    Join Date
    06-12-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    167

    Re: Dynamic Combo Box list length with 2 columns

    Good to know thanks

  11. #11
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Dynamic Combo Box list length with 2 columns

    My pleasure

+ 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. [SOLVED] Drop down list using combo box and dynamic named ranges
    By Pauly K in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-21-2017, 06:11 AM
  2. [SOLVED] Add new item to Combo Box Dynamic List
    By Craig2015 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-11-2015, 08:03 AM
  3. Replies: 4
    Last Post: 08-21-2015, 03:44 PM
  4. [SOLVED] Populate userform combo box with named dynamic list
    By HeyInKy in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-28-2014, 05:17 PM
  5. Replies: 4
    Last Post: 01-26-2014, 12:33 PM
  6. Dynamic Row / Column change on List Box / Combo
    By vt1 in forum Excel General
    Replies: 7
    Last Post: 05-23-2013, 06:43 AM
  7. Combo box with dynamic list using VBA
    By hsejar in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-25-2011, 11:49 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