Hi Don,
You possibly know some of the following but if not then the information is
essential to the answer.
Firstly there are 2 types of List Boxes. In xl2007 when inserting controls
you will see them as Form Controls or ActiveX controls. In earlier versions
the form controls are on the Forms Toolbar and the ActiveX controls are on
the Control Toolbox toolbar.
To make property changes to ActiveX controls, you need to be in Design Mode
which is toggled on and off with a button and icon like a blue set square and
a ruler and pencil. When the control is first created, Design mode turns on
by default. Don't forget to turn it off after finishing the design of the
control.
To make multiple selections in a Test Box you will need to have a command
button to run the code after the selections are made. The following code is
for ActiveX type List Boxes and ActiveX type Command Button. To insert the
code, create the ActiveX command button and while in Design Mode, right click
the command button and select View Code and insert the code between the
Private Sub and End Sub lines that are automatically created.
The code is extracted from a previous post by MVP Dave Peterson.
Dim DestCell As Range
Dim iCtr As Long
With Worksheets("sheet1")
Set DestCell = .Range("A1")
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(1, 0)
End If
Next iCtr
End With
