+ Reply to Thread
Results 1 to 9 of 9

Combo box across multiple cells

  1. #1
    Registered User
    Join Date
    06-01-2016
    Location
    Singapore
    MS-Off Ver
    13
    Posts
    55

    Combo box across multiple cells

    Hello,

    I need to add combo box(ActiveX Control) or Data Validation as drop down list. I have a range of 15 values like, high, low, medium,etc... Have created named range called "priorityvalue".

    I can create a dropdown list using combo box by adding named range under ListFillRange in the properties or data validation list by giving named range.

    But my concern, I need to add drop-down for 58cells with same values mentioned above on one Column. All 58 Cells on same column. Its tedious job to create combo box for all cells. Please suggest me better option here.

    Data validation list serves the purpose. However, it makes user to scroll through dropdown list on each cell unlike combo box it has no input box..

    Please suggest

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Combo box across multiple cells

    I know of one workaround for this using Data Validation but it's a little ugly.

    Set up your 15 values in the same column and on the same sheet where you want to put your dropdowns, before the first dropdown. Then set up your Data Validation cells immediately below the list. You can hide the rows containing the list to make things look better. I do not know why this works but it will give you autocomplete in the Data Validation cells.

    If this explanation is not clear or you can't get it to work please attach your file.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    06-01-2016
    Location
    Singapore
    MS-Off Ver
    13
    Posts
    55

    Re: Combo box across multiple cells

    Thank you so much for your workaround. I understood what you meant and I tried it. However, it was not working on certain values. Just to make sure I am not doing anything wrong here I am attaching sample sheet as the original sheet is protected one.
    Attached Files Attached Files

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Combo box across multiple cells

    You do not have the name priorityvalue defined, and you do not have any cells with data validation. I took your list and set data validation up.

    I see what is happening. In Excel, when you enter data in a column, Excel will autocomplete when you type data lower down in the same column. That is why this little trick works (I didn't realize that until now). It does this for any cells; it has nothing to do with data validation. When you add data validation to a cell, it does the same thing. That is why the list has to be in the same column above the cell.

    But the autocomplete that Excel performs in this situation is not the same as what it does in comboboxes. In a combobox, Excel finds the first entry that matches what is typed so far. For the "data in the column" type of autocomplete, Excel will not autocomplete an entry until you have typed enough characters to make a positive match. That is, if you have

    Sender OR Recipient OR Mailbox Owner
    Sender OR Recipient OR Driven
    Sender OR Recipient OR Mailbox

    And you start typing "Sender OR Recipient OR ", Excel does not yet have enough characters to know for sure which one matches. If you type D, it will show you the second entry. If you type M, it still doesn't know which one you are typing. That is what you are seeing as "not working on certain values."

    (By the way your file has a typo in the third one)

    So the only way to get combobox behavior is to use comboboxes. You can use macros to create the comboboxes. How are you using the data in the comboboxes? I can take a look tomorrow if time permits (I am in the Washington DC/New York City time zone).

    It might be possible to use macros to execute autocomplete in regular cells, but it requires handling key press events since there is no other built-in event to tell you a user is typing in a cell. This gets a little complicated and I don't have experience with that.

  5. #5
    Registered User
    Join Date
    06-01-2016
    Location
    Singapore
    MS-Off Ver
    13
    Posts
    55

    Re: Combo box across multiple cells

    Thank you. Yeah, there might be few typos as its not the actual spreadsheet. I tried to copy it down from the actual one ( I am restricted to send the actual one). Meanwhile, I have found one of the macro. But I am not able to pass the values correctly on the range of cells to get that work.
    Please Login or Register  to view this content.
    Please let me know where am I going wrong. I inserted a new module and trying to call this function from cell using =AddComboBoxToColumns(E1:E57). But here i am doing something wrong in passing values which I am not able to figure it out. Cells Range E1:F57 is where I need a combo box. Can you please correct me?

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Combo box across multiple cells

    Normally you don't use a Function unless you want it to return a value. In this case it is not returning a value. Also a Function is not allowed to change a cell that it is not called from, so I don't know if this could work.

    Since this is a one-time thing use this code in a Module (it is probably in one now). Then from your worksheet, go to Developer, Macros, find this macro and Run it.

    I tested this successfully but you will have to update your list of values. Also note that it sizes the comboboxes to match the size of the cells where they will be placed, so size the cells in advance.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-01-2016
    Location
    Singapore
    MS-Off Ver
    13
    Posts
    55

    Re: Combo box across multiple cells

    Wow!! that worked absolutely fine!! thank you so much.

  8. #8
    Registered User
    Join Date
    06-01-2016
    Location
    Singapore
    MS-Off Ver
    13
    Posts
    55

    Re: Combo box across multiple cells

    One last question:How do I reset the contents of combo box when ever I open the workbook? I dont want to see the previous selected value that used when workbook was saved last time. I want a blank cell when ever I open the workbook.

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Combo box across multiple cells

    You have to use VBA to blank them out when you open the workbook. See below.

    How are you using the values in the comboboxes once the user has completed them? It does not look like the values are linked to cells; what are you doing with the values? And if someone is going to the trouble to make all those selections, why do you want to lose them all every time you open the file?

    Here is the answer but I would still like to understand how you are using this to give you the best guidance. Put this code into the ThisWorkbook module:
    Please Login or Register  to view this content.

+ 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. Replies: 8
    Last Post: 06-28-2014, 03:15 PM
  2. [SOLVED] Excel 2010 VBA – Combo Box Options– Clear cell/box in 2nd Combo when 1st Combo selected
    By theshybutterfly in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-17-2013, 07:25 PM
  3. Populate active x combo box to multiple cells
    By cookielady in forum Excel General
    Replies: 6
    Last Post: 12-01-2013, 11:06 PM
  4. Replies: 0
    Last Post: 04-30-2013, 02:30 PM
  5. Linking Combo Box multiple cells
    By zero3ree in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-04-2011, 03:41 PM
  6. Replies: 1
    Last Post: 01-12-2005, 12:06 PM

Tags for this Thread

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