Excel List Box Output

[geekette]

Hi,

I have created a list box using the Control Toolbox. It's Listfill range is looking up a named range I have created called LOOKUP_Skills. I have set it to Multi select items.

What I now need to do is output any items selected in the list to another range in my workbook so I can then run a query based on these selections. I have been reading lots of online advice but all seem to point to list boxes that have been created on user forms.

Any help would be very happily received :O)

 

[sulakvea]

hi, you could embed the same controls (in this case listbox) into the sheet from the VBA menu - and all the things you read about userform controls will pretty much apply to those as well. the only difference being, when you refer to those objects from other sheets / modules, you have to indicate Sheet1.Listbox1, instead of just Listbox1.

right click the menu and enable Visual Basic. you will see a similar toolbox

 

[Andrew Poulsom]

TheListBox from the Control Toolbox is the same as the one you can add to a UserForm. What have you tried?

 

[sulakvea]

P.S. after reading Andrews post i noticed you talking about the control toolbox.

i originally assumed you meant the forms toolbox for simple drop-downs and buttons - you cant do much with them.

you should be able to use most, if not all, of what you read about the objects from control toolbox.

 

[geekette]

Hi Andrew,

I have found quite a few pieces of code (most I'm not 100% sure what they are doing). I have found this which doesn't relate to a user form but I get a compile error (Next without for)

Dim DestCell As Range
Dim iCtr As Long

With Sheets("View My Requests")
Set DestCell = .Range("G" & inextrow)
End With

With Me.lstSecondProd
DestCell.Resize(.ListCount, 1).ClearContents

For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(0, 1)
End If
Next iCtr
End With

 

[Andrew Poulsom]

That code compiles fine for me.

 

[geekette]

Are you able to advise how I might trouble shoot why it's erroring for me?

 

[Andrew Poulsom]

Please post the entire code that is giving a compile error.

 

[geekette]

Private Sub ListBox1_Change()

Dim DestCell As Range
Dim iCtr As Long

With Sheets("View My Requests")
Set DestCell = .Range("G" & inextrow)
End With

With Me.ListBox1
DestCell.Resize(.ListCount, 1).ClearContents

For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(0, 1)
End If
Next iCtr
End With

Next i
End Sub

 

[Andrew Poulsom]

Remove the last line:

Next i