+ Reply to Thread
Results 1 to 16 of 16

List of the combobox to be displayed starting from the value that you are introducing

  1. #1
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    16

    List of the combobox to be displayed starting from the value that you are introducing

    Hello everybody,

    I'm trying to figure out how to change the display options of a combobox list. As it is now, the actual combobox list goes to the bottom when matching with an existing value while you are in the process to introduce a new value.

    In this example I start to introduce the value "M6" and goes to the bottom of the displayed values that starts to match with the value that I introduce, but what I would actually like, is to see as first the value that starts to match in the existing list and below the other values as you can see attached:

    Combobox list Sample.png

    How can I change this? What I need to put in the combobox code?

    Thank you all in advance!
    Last edited by thorblow; 12-04-2020 at 08:59 AM.

  2. #2
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,220

    Re: List of the combobox to be displayed starting from the value that you are introducing

    Is this what you want ?
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    16

    Re: List of the combobox to be displayed starting from the value that you are introducing

    Yes, this could work... But the thing is how to put it in the existing code I have.... I actually don't use an userform, I have a combobox added directly on the sheet and then I have this attached code which I copied from another site but edited for my needs. This code is pasted into the active sheet code and the main goal is to use a combobox to handle the data from a Data validation list.

    Do you know what I need to change/add in this code in order to do the same you did in your userform?

    Please Login or Register  to view this content.
    Last edited by thorblow; 12-04-2020 at 09:59 AM.

  4. #4
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,220

    Re: List of the combobox to be displayed starting from the value that you are introducing

    i will take a look after you have complied with rule regarding code tags.

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

  5. #5
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    16

    Re: List of the combobox to be displayed starting from the value that you are introducing

    Thanks again for your fast reply and sorry for the code tags rule. I have just edited the previous message, hope that now is OK!

  6. #6
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,220

    Re: List of the combobox to be displayed starting from the value that you are introducing

    without the actual file i am struggling to apply it

  7. #7
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    16

    Post Re: List of the combobox to be displayed starting from the value that you are introducing

    For some reason I can't attach the file. The button doesn't work.

    However, I have attached as an image:

    !Book1.xlsm

    Thank you again for your effort and patience!!

    Have a nice weekend!
    Last edited by thorblow; 12-04-2020 at 01:02 PM.

  8. #8
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,220

    Re: List of the combobox to be displayed starting from the value that you are introducing

    I have put the combobox directly on the sheet.
    Incorporating it into the existing code proves impossible.
    In fact I would get rid of the existing code and just use the attached file added to your sheet.
    Note there is code in the Workbook module as well as the Sheet module
    Best of luck.
    torachan.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    16

    Re: List of the combobox to be displayed starting from the value that you are introducing

    thanks, I will take a look next week at work!

  10. #10
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    16

    Re: List of the combobox to be displayed starting from the value that you are introducing

    Thank you for your effort Torachan. You certainly achieved to do it, but unfortunately, I really need to use the data validation list as a source of the list to be displayed and then run the macro that you saw in the previous Excel. This is a must that I can not change because I have multiple cells that activates (unhides) the combolist... therefore your solution is not suitable for me... As per your explanation seems unlikely to achieve it with my demanding to use data validation list as a source, but well, if you or somebody else come with a new idea I will be more than glad to check it out again!

    Thanks again for your help!

  11. #11
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    824

    Re: List of the combobox to be displayed starting from the value that you are introducing

    Hi, thorblow
    You can use a single combobox applied to multiple cells (without data validation).
    Here's an example:

    Let me know if you're interested in this method.

  12. #12
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    16

    Re: List of the combobox to be displayed starting from the value that you are introducing

    Hi Akuini!

    Thanks for this new proposal! Seems to be that this could work, however there is an issue when using this method.

    The worksheet that you attached, shows the values in rows, but I need to introduce the values in columns, which are merged!

    Capture.JPG

    So, in order to do a fast test, I went into the code of sheet 1 and I changed the range where the combobox needs to be displayed from the existing B2:B20 to C2:E7 meaning 3 merged columns: First from c2:c7, second from d2:d7 and third from e2:e7) and it turned out that the combobox won't activate when using merged cells. Is there a way to do it?

    Finally, is there also a way to introduce data from the combobox to the active cell pressing enter as it is now, but also when selecting directly the value when you click it with the mouse? Could be possible to have it both ways? (enter and mouse click?)

    Thanks in advance for your help!
    Last edited by thorblow; 12-14-2020 at 06:14 AM.

  13. #13
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    824

    Re: List of the combobox to be displayed starting from the value that you are introducing

    Your attachment doesn't work. Dont use the paper clip icon to attach it but use Manage Attachments to open the upload window (please read the yellow banner at the top of this page).

    I don't think the combobox will work with merged cells.
    Is the merged cells really necessary?
    Merged cells can cause many problems, so it's better not to use it.

  14. #14
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,220

    Re: List of the combobox to be displayed starting from the value that you are introducing

    "you can lead the mule to the water trough but you can not make it drink".
    best advice ever in the link below 'how to use merged cells effectively'
    torachan.


  15. #15
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    16

    Re: List of the combobox to be displayed starting from the value that you are introducing

    Well, guess what, the mule was that thirsty that at the end something drank

    And still a second guess what, I did it with your help torachan :D :D

    I used part of your code torachan and although the resultant final code is a little bit messy, it works pretty good, the only thing is that I need to introduce the values without the "M" in order to respect the desired view of the displayed list.

    You double tap the cell, combobox appears and then you just introduce the value of the reference you need without the "M" and that's it.

    So, the merged cells thing is not an issue anymore, simple and effective :P :P You might even remember me easily after that

    I will leave the code as it is here, therefore we can mark this thread as solved

    Thanks again to you all for your time and effort!
    Attached Files Attached Files
    Last edited by thorblow; 12-14-2020 at 09:24 AM.

  16. #16
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,220

    Re: List of the combobox to be displayed starting from the value that you are introducing

    Be carefull, merged cells bite the unwary
    torachan.

+ 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. List manipulation, how to change the starting point in a list of set numbers
    By Harri05 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-12-2020, 11:19 AM
  2. [SOLVED] ComboBox.Clear ...displayed values in the ComboBox dissappear
    By buhnen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-25-2016, 10:32 AM
  3. [SOLVED] Modify Userform to highlight or unhighlight displayed ComboBox Value
    By Brawnystaff in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-24-2014, 04:08 AM
  4. [SOLVED] VBA code for introducing hours in ComboBox
    By pezalmendra in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-22-2012, 05:21 PM
  5. Styles from Activeworkbook need to be displayed on ribbox comboBox
    By srinivas1104 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-18-2011, 07:13 AM
  6. Combobox Displayed search
    By D_Rennie in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 06-19-2009, 02:37 AM
  7. [SOLVED] [SOLVED] Set string variable to word displayed in a combobox
    By Graham Whitehead in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2006, 09:30 AM

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