Results 1 to 3 of 3

help with dynamic combo box

Threaded View

  1. #1
    Registered User
    Join Date
    07-31-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    3

    help with dynamic combo box

    Hi, i want to additems to combobox whenever my userform initialize.
    I chose to use the list property as i have a very long list and using .additem not as efficient.
    What i want my program to do is go to a particular column, then get any data from that column (excluding the column name) and add it to my combo box.
    Basically i think my program logic works, but i am not so familiar with excel practices.

    main issue is why this 2 lines of code will fail me:
    productA = Range("P2", "P3")
    comboBox1.List() = productA
    Below is the coding i got.
    'productA is the list of items i want to add
    Dim productA as Variant 
    Dim temp As Range
    'search for the column with name "Category A"
    Set temp = Selection.EntireRow.find("Category A", LookIn:=xlFormulas, lookat:=xlWhole).Offset(1, 0)
    'search the column for any filled data and put it in the productA as a list
    productA = Range(tp, tp.EntireColumn.find("", LookIn:=xlFormulas, lookat:=xlWhole).Offset(0, 0))
    'using list property to add items to combo box
    comboBox1.List() = productA
    error occurs when my productA list is empty. so i debugged and simplified the code to simulate situation when list should be empty and when list contains 1 item:

    Dim productA as Variant 
    productA = Range("P2", "P2")
    comboBox1.List() = productA
    error occurs when list is "empty".

    Dim productA as Variant 
    productA = Range("P2", "P3")
    comboBox1.List() = productA
    no error occurs when at least 1 item exist.

    Btw the error message is
    Run-time error '381'
    Could not set the List property. Invalid property array index.

    Anyone knows why error occurs when productA is set to Range("P2", "P2")? Thanks alot.
    Last edited by chia0089; 07-31-2009 at 02:40 AM. Reason: mistake when typing here : changed to ,

Thread Information

Users Browsing this Thread

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

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