Combobox ListFillRange

[dave8]

Is it possible to use the Combobox ListFillRange to list values from two groups of cells? For example, in column 'A', I have the values, 1,2,3; and in Column 'B', I have the values, A, B, C. I want to list the values in column 'A' along with the value of say, B, in Column 'B'.

combobox.listfillrange = "Sheet1!A1:A3" < ---- how to include value B from column B in this list? Or, how to get value A from column B?

 

[Norie]

Why not just extend the range and set the combobox to 2 columns?

Combobox1.ColumnCount =2
 
Combobox1.ListFillRange = "Sheet1!A1:B3"

This should fill the combobox with the data but I'm pretty sure it's not going to give you the results you want.

A combobox isn't really designed to show multiple columns of data.

 

[dave8]

I think the problem is that I'm going to get all the values in my ListFillRange in Column B. I want to get the list of values from Column 'A' but just *one* of the value in Column B.

 

[Norie]

Sorry but I don't understand - are you sure you want to use a combobox for this?

Where is this 'one' value going to end up?

Do you mean you want to allow the user to select something from the combobox and return the corresponding value from column B?

 

[ChrisM]

You probably need to load the combobox manually, either at the start of your macro or upon workbook_open.

Loop through however many ranges you want, and use:

For each cell in MyListRange1
   combobox1.additem  cell.value
Next
For each cell in MyListRange2
   If cell = "B" Then combobox1.additem  cell.value
Next

 

[dave8]

I may not be clear about my intent. Let me try explaining this again. I'm using a combobox. And, on a worksheet, Column 'A', I have these values: 1, 2, and 3. Also, in Column 'B', these values: 8, 9 and 10, respectively. The VBA code to reference the values from Column 'A' looks like this: combobox.listfillrange = "Sheet2!A1:A3" . The drop down of this combobox will list the values I have in Column 'A'; values 1, 2, 3. But, what I want is to include a value from Column 'B', say, 9, so that my drop down list contains the values: 1, 2, 3 and 9. The value 9 will be taken from Column 'B'. Thus, I want to list the range in Column 'A', but also include some value in Column 'B'.

 

[Norie]

Eh, why?

Usually you would only populate something like a listbox from one column and you definitely can't do what you seem to want using ListFillRange.

 

[dave8]

I can understand why this doesn't seem to make sense given the example. I have a program that contains a larger list of values and additional comboboxes and I don't want to have to reorder my list to include those values from another column and put them into the same column.

 

[Muddock]

Sorry, new user and I don't see how to ask questions. So I am typing in here.

I am simply trying to dynamically populate an ActiveX combobox. I have tried all the ideas in this thread and those on many other sites and none of them work.

My box is called FileButton, my worksheet is called wsMain and I can find no way of referring to it. If I refer to wsMain.FileButton Excel doesn't recognise it. I have tried using the OLEObject collection and the shapes collection but then I am told that my box does not have the ListFillrange property. What am I doing wrong?