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

 

[mikerickson]

try

With ActiveWorkbook
    .Worksheets("Dashboard").ListBox_Caption.ListFillRange() .Worksheets("Captions").Range("A1").CurrentRegion.Address
End With

 

[sulley333]

That coded didn't give me any errors but there is nothing populated in the list box

 

[Norie]

You cannot refer to a listbox like that - it isn't a property of the worksheet.

What type of listbox is it exactly? Forms or ActiveX (control toolbox)?

Also, why are there parentheses after List and ListFillRange?

 

[Norie]

All of the following worked.

Standard module:

Option Explicit
 
Sub PopulateListBoxesStdModuleCodeNames()
Dim ctl
    ws_dash.ListBox_CaptionX.List = ws_caption.Range("A1:A10").Value
    Set ctl = ws_dash.Shapes("ListBox_Caption")
    ctl.ControlFormat.List = ws_caption.Range("A1").CurrentRegion.Value
End Sub
 
Sub PopulateListBoxesStdModule()
Dim ws As Worksheet
Dim ctl
Dim ctlX
    Set ws = ws_dash ' Worksheets("DashBoard")
     Set ctl = ws.Shapes("ListBox_CaptionX").OLEFormat.Object
    ctl.Object.List = ws_caption.Range("A1").CurrentRegion.Value
 
    Set ctlX = ws.Shapes("ListBox_CaptionX").OLEFormat.Object
 
    ctlX.Object.List = ws_caption.Range("A1:A10").Value
 
End Sub

Option Explicit
 
Sub PopulateListBoxesWSModule()
Dim ctl
    ListBox_CaptionX.List = ws_caption.Range("A1:A10").Value
    
    Set ctl = Shapes("ListBox_Caption")
    ctl.ControlFormat.List = ws_caption.Range("A1:A10").Value
End Sub

 

[sulley333]

Hi Norie!

I am using a list box from the active x box. I am not sure which code above to use. I have tried all of them and none of them seem to work. I either get a permission denied or object or method error. The reason I had parenthesis was because I was modifying code I had made to populate a combo box and that is what I thought was needed.

Thanks!

 

[sulley333]

So, I got it to work using the code:
PopulateListBoxesStdModule

Now my only hiccup is that I can't seem to get it to do a dynamic range because I won't always know the number of rows on the Caption worksheet.

Any help on that would be fabulous!!

Thanks!

 

[Norie]

Which part of the code are you using?

I know some of the ranges are hardcoded but there are also dynamic ranges.

Those are the ones that use CurrentRegion, so you should just be able to use that.

If that doesn't work post back with the code you are using and details of how it isn't working.

 

[sulley333]

Thanks so much for all of your help...I just found the row using a count and was able to use that variable as the ending part of my range. Sorry to keep bothering you but I am trying to capture the output of the selection into a cell and below is the code I was using but doesn't seem to be working:

Private Sub Listbox_Caption_Comp_Change()
Dim i As Integer
Dim Msg As String
With ListBox1
  For i = 0 To Listbox_Caption_Comp.ListCount - 1
    If Listbox_Caption_Comp.Selected(i) = True Then
        If Msg = "" Then
          Msg = .List(i)
        Else
          Msg = Msg & ", " & .List(i)
        End If
    End If
  Next i
End With
With ActiveSheet
  .Range("H14").Value = Msg
End With
 
End Sub

 

[Norie]

I think you might know what I'm going to ask - how is it not working?

As far as I can see the code is fine, the only possible problem I can see is that the outpupt is going to the wrong worksheet.

Try explicitly referencing the worksheets you want the selected items to goto.

Worksheets("Dashboard").Range("H14").Value = Msg

PS You do want all the values to appear in one cell?

 

[sulley333]

When it gets to Msg = .List(i) it says Object required