combo box to list box

[sulley333]

I need to change a combo box on my sheet to a list box because I need multi-select functionality. I am not sure how it is different to load it. This is the code that I had to load the combo box and I just plugged in the list box name but it isn't working...any help?

ws_dash.ListBox_Caption.List() = ws_caption.Range("A1").CurrentRegion.Value

 

[Domenic]

It looks like it doesn't accept a reference to a worksheet variable. Try the following instead...

If the code exists within the sheet module:

Me.ListBox_Caption.List() = Me.Range("A1").CurrentRegion.Value

If the code exists within a regular module:

ActiveSheet.ListBox_Caption.List() = ActiveSheet.Range("A1").CurrentRegion.Value

or

Sheets("Sheet1").ListBox_Caption.List() = Sheets("Sheet2").Range("A1").CurrentRegion.Value

 

[sulley333]

In my code above ws_dash and ws_caption refer to the worksheets of where the box is at ws_dash and where the list of values is ws_caption. I am not sure how the above code differs. Am I missing something?

 

[sulley333]

It is also telling me that permission is denied...is there something else going on?

 

[Norie]

What's the error?

Is ws_dash the codename for the worksheet with the listbox on it?

One thing that might be causing problems is the use of List.

On a userform you can use that to populate a listbox with values from a range but you can't seem to do that with a listbox on a worksheet.

Try using the ListFillRange property instead.

 

[sulley333]

The listbox is on a worksheet that is called ws_dash in the code. I changed the List to ListFillRange and the error said type mismatch. Sorry, just don't have much experience with the listboxes.

 

[Domenic]

If ws_dash and ws_caption are code names for your sheets, and ListBox_Caption is the name of your listbox, then it should succeed. I tried testing it and it seemed to work.

I tried testing it again using a worksheet variable as the qualifying reference and again I got a compile error (Method or data member not found) with .ListBox_Caption highlighted.

 

[sulley333]

It isn't working...I am getting a Type Mismatch error...here is the code that I used:

ActiveWorkbook.Worksheets("Dashboard").ListBox_Caption.ListFillRange() = ActiveWorkbook.Worksheets("Captions").Range("A1").CurrentRegion.Value
    If ActiveWorkbook.Worksheets("Dashboard").ListBox_Caption.ListCount > 0 Then ActiveWorkbook.Worksheets("Dashboard").ListBox_Caption.ListIndex = 0

Any other ideas?

 

[mikerickson]

What is in ActiveWorkbook.Worksheets("Captions").Range("A1").CurrentRegion.Value

 

[sulley333]

It is a list of values in column A of Captions worksheet