Excel List Box Output

geekette

Board Regular
Joined
Sep 12, 2006
Messages
120
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)
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

sulakvea

Well-known Member
Joined
Jul 2, 2008
Messages
994
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
TheListBox from the Control Toolbox is the same as the one you can add to a UserForm. What have you tried?
 

sulakvea

Well-known Member
Joined
Jul 2, 2008
Messages
994
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

Board Regular
Joined
Sep 12, 2006
Messages
120

ADVERTISEMENT

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
 

geekette

Board Regular
Joined
Sep 12, 2006
Messages
120

ADVERTISEMENT

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

geekette

Board Regular
Joined
Sep 12, 2006
Messages
120
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top