Getting stuff out of Multiselection Listbox

  • Thread starter Thread starter Curious George
  • Start date Start date
C

Curious George

I wanted to use a multiselection listbox in one calculation chart and
everything was going fine until I realised that I don't know anything
about reading out the selections from the listbox. Can someone help me?
I need something simple like few lines on the listbox and for each
selected boolean TRUE is written to the sheet in corresponding cells.
 
On your Control toolbar, make sure you are in design mode then click the
Properties icon. If your ListBox is not listed in the drop-down box at the
top, either click on it or select it using the drop-down.

You may then need to click on the Listbox

Link Cell and List Fill range are in the Misc section.

To link A1 to a List in B1:B7:

type A1 in the Link Cell and
B1:B7 in the List Fill range

HTH
 
AltaEgo said:
On your Control toolbar, make sure you are in design mode then click the
Properties icon. If your ListBox is not listed in the drop-down box at
the top, either click on it or select it using the drop-down.

You may then need to click on the Listbox

Link Cell and List Fill range are in the Misc section.

To link A1 to a List in B1:B7:

type A1 in the Link Cell and
B1:B7 in the List Fill range

HTH
Click to expand...

Nope, gave a #N/A error, but I figured how to do the stuff in VBA. But
now I have a problem with the VBA code itself. I put my code in the
Click event of the ListBox, but it executes the code under that
subroutine only when I press the macro run button in the code editor.
Can I make the code run automatically the subroutine every time user
presses the ListBox items?
 
Did you try using a newly created listbox?

Instructions below may be clearer than mine:

For Excel 2003:
http://excel.tips.net/Pages/T002918_Using_List_Box_Controls.html

For Excel 2007:
http://support.microsoft.com/kb/291073

If you are still having problems, try modifying this.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded

'get listbox name from listbox properties and replace ListBox1
ActiveSheet.Shapes("ListBox1").Select
With Selection
.ListFillRange = "$G$2:$G$11" 'relevant range
.LinkedCell = "$H$1" 'relevant cell
End With
End Sub


NOTE: Deleting list boxes and recreating new ones results in the same
default listbox name being used again. Any code you wrote in the previous
listbox may still be attached when you create a replacement. So, right-click
and delete any remnants.
 
Back
Top