Multi Select List Box - Checkbox

[Paul]

I want to make my list box available for multi selection using checkboxes - I
know it must be easy, but I'm having a bad day and can't work it out.

 

[Joel]

Private Sub CheckBox1_Click()
ActiveSheet.ListBox1.MultiSelect = fmMultiSelectMulti
End Sub

 

[Paul]

Ah ! I've tried using this format of code in the Initialisation code of a
form, but it keeps failing with a message 'Member of data member not found'

Is this a case of not having the code run in the right place ? If so, what
is the right place ?

 

[Joel]

I think there are 3 different answers to your question because I'm not sure
where the code is located and which type form you are refereing to.

there are two different type forms, one is a worksheet and the other is the
VBA Userform. I suspect you are using the VBA form where you may need to
reference the form name as well as the listbox name.


1) You may not have the right sheet name or listbox name. The names of the
listbox can be renamed from the property window or through code. If you are
in a worksheet you need to get into Design Mode and right click the Listbox
to changge names or check the name.

enter Design Mode by the following
a) worksheet menu View - toolbars - Control Toolbox. click on the
Triangle to enter or exit design Mode

2) Check the properties of a VBA form. From VBA menu View - Properties
Window. Check name property. by the way you can manually vchange the List
box Multiselect property from the property window on wither the worksheet of
the VBA form property.


3) I think your problem if you are using worksheet VBA code, then you need
to use
Userform1.Listbox1.MultiSelect = fmMultiSelect


You can put initialization code for a listbox in the initilization code for
the userform

Private Sub UserForm_Initialize()
UserForm1.lstNames.AddItem "Test One"
UserForm1.lstNames.AddItem "Test Two"
UserForm1.txtUserName.Text = "Default Name"
End Sub

when the code say private it means it has to be inside the userform. If you
right click the userform in VBA and select view code is where you want to put
this code. A private function in a userform will recognize Listbox1 without
needing the Userform1 because the Listbox is part of the Userform. if you
are using two userforms then if you are referencing the 2nd Userfrom list box
from the first you need to include the Userform Name. The Userfrom Names are
not private because they need to be recognized from the worksheet put the
control objects in the form are usually private.

 

[Paul]

Joel, Many thanks for your speedy response.

I am using a VBA form, and I've added the form name to the coded, but still
without any success.

It is beginning to dawn on me that the issue is that, having specified
ListStyle as Option, I expected to see a property 'Multi' in the properties
window, but none is shown.

Just to be clear, I'm using Excel 2003.

 

[Joel]

I think you are looking at the Userform property and not the Listbox
Property.
after opening up the userform click on the Listbox to see its properties. I
can see the multiselect property on a listbox using both multiselect
properties.

It should be when you type the name of the Userform in the VBA code and then
the period the listbox name that you created should be in the list that
appears in the pop up box. Then when you type the listbox name and the
period the multiselect option will appear.

 

[Paul]

When I type the names, etc. as you suggest, no MultiSelect option is available.

Is it possible that this is a service pack or update issue ?

 

[Joel]

I'm not sure you really have a Listbox. Try to delete the Listbox and
re-add. Then change the Name of theListbox so it is the same name as the
original box. A real Listbox shold have the property. You could of call a
Textbox a Listbox and really get confused.

 

[Dave Peterson]

Option Explicit
Private Sub UserForm_Initialize()
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption
.AddItem "A"
.AddItem "B"
.AddItem "C"
End With
End Sub

Worked ok for me.

 

[Joel]

Sub splitSheet()

InitStr = UCase("Initializing server")
With Sheets("Sheet1")
RowCount = 1
FirstRow = RowCount

Do While .Range("A" & RowCount) <> ""
StrPos = InStr(UCase(.Range("A" & RowCount)), InitStr)
If .Range("A" & (RowCount + 1)) = "" Or StrPos > 0 Then

If StrPos > 0 Then
LastRow = RowCount - 1
Else
LastRow = RowCount
End If

If LastRow <> 0 Then
Set newsht = Worksheets.Add(after:=Sheets(Sheets.Count))
.Rows(FirstRow & ":" & LastRow).Copy _
Destination:=newsht.Rows(1)
End If
FirstRow = RowCount + 1
End If
RowCount = RowCount + 1
Loop
End With
End Sub

 

[Joel]

Dave: Read the other postings. Right now he tried your suggestion. Paul
doesn't have the multiselection option and the Liststyle doesn't effect the
problem. the only thing that makes any sense is if he took a different type
object (like a textbox) and called it a listbox.

 

[Paul]

I can confirm that the object on my form is a listbox. I've tried deleting,
re-adding and I still get no MultiSelect property. I'm beginning to think
that my copy of Excel 2003 is flawed somehow, but I'm already up to Service
Pack 3, and I can't find any other downloads to fix tghe problem on the MS
site.

Next step is to try it on one of our other machines - I'll let you knoiw the
result.

The only good thing is that it appears I'm not as stupid as I thought I was
when this wouldn't work in the first place.

 

[Joel]

I'm using

Microsoft Excel 2003 (11.8211.8202) SP3

Also check on VBA Menu Tools - References. These are the library I'm using
a don't havve your problem.

Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office Object Library
Microsoft forms 2.0 Object Library.

 

[Paul]

Things are becoming a little clearer.

My version is 11.8169.8172.

I've been asking the wrong question all along. It should have been - how do
I upgrade my version !!

 

[Paul]

And at last - all becomes clear.

Update with SP3, but then found that I am as stupid as I first thought.

All along I've been referring to a combobox set with a listbox property,
rather than a listbox !!

Now it works - with one small issue - how do I get the selected data back
out from each line of the box ?

 

[Paul]

Joel

Many thanks for your patience. Problem solved - system working beautifully.