+ Reply to Thread
Results 1 to 7 of 7

Dynamic Combobox item, Shorted & No Duplicate

  1. #1
    Forum Contributor
    Join Date
    12-01-2015
    Location
    Indonesia
    MS-Off Ver
    MS Office 365
    Posts
    109

    Dynamic Combobox item, Shorted & No Duplicate

    Dear All,

    What is the VBA script for dynamic combobox list item that will change by the date on E2 and shorted with no duplicate? for example, if date 17-May-2017 then on combobox list item will be "aaa,bbb,ccc".

    Thank you

    Omega Boost
    Attached Files Attached Files

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Dynamic Combobox item, Shorted & No Duplicate

    Instead of using a Form Control, try ActiveX control which is easier and flexible to work with.

    Place the following code on Sheet Module and enter a date in E2 to see if the combobox is populated with the correct items.
    Please Login or Register  to view this content.
    For details, refer to the attached.
    Attached Files Attached Files
    Last edited by sktneer; 05-21-2017 at 07:01 AM.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Dynamic Combobox item, Shorted & No Duplicate

    Why is an ActiveX any easier for this scenario? Must be missing that bit as in general one should avoid ActiveX controls on worksheets wherever possible

  4. #4
    Forum Contributor
    Join Date
    12-01-2015
    Location
    Indonesia
    MS-Off Ver
    MS Office 365
    Posts
    109

    Re: Dynamic Combobox item, Shorted & No Duplicate

    Dear sktneer,

    Thank you for the solution and it's perfect. Just wondering is it possible to set x range without using CurrentRegion? Cause i will apply this script on table that have several columns, and the date column might be not in the first column. Thank you

  5. #5
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Dynamic Combobox item, Shorted & No Duplicate

    You're welcome!

    In that case you can try like x = Range("A1:E500").Value or declare a variable to hold the rows on the sheet dynamically and then try x = Range("A1:E" & lr).Value where lr is the last row used on the sheet.

    In the line If x(i, 1) = Target.Value Then, 1 here assumes that the date is the first column and if the date column is not the first column, you must change it as per your requirement.

  6. #6
    Forum Contributor
    Join Date
    12-01-2015
    Location
    Indonesia
    MS-Off Ver
    MS Office 365
    Posts
    109

    Re: Dynamic Combobox item, Shorted & No Duplicate

    Dear sktneer,

    Many Thanks for your help. I already apply your script as per your advice and it's work perfectly.

    Regards

    Omega Boost

  7. #7
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Dynamic Combobox item, Shorted & No Duplicate

    You're welcome Omega! Glad it worked.
    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. [SOLVED] VBA To populate listbox with duplicate item by increasing the item quantity
    By sintek in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-01-2016, 07:51 AM
  2. selecting an item in combobox, the item does not get selected
    By AVM in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-14-2014, 05:14 AM
  3. Combobox source , linked to other combobox value ??(DYNAMIC)
    By stevnb in forum Excel Programming / VBA / Macros
    Replies: 40
    Last Post: 01-25-2013, 05:28 AM
  4. Combobox: How to determine the location of the selected item if combobox has rowsource
    By ahsanzafar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-11-2012, 03:24 PM
  5. ComboBox-ADD ITEM
    By CESAR V. ARROYO in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-02-2011, 04:48 PM
  6. [SOLVED] Don't add item in combobox ...
    By Marie J-son in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-09-2005, 11:10 AM
  7. [SOLVED] Combobox Add Item
    By MBlake in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-02-2005, 12:06 PM

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