How to Pre-Select items in Multi-Select List on Form

[MikeZz]

Hi,
I have a worksheet that has 2 lists...
buildList is a "master" list containing all possibilities for a field (like
validation).
currentList is active subset of the "master" list.

Instead of modifying currentList manually, I want a form to pop up that
shows a list of all the possibilies (buildList), but also pre-selects all the
items in the currentList.

My code below adds all the items properly, but I can't figure out how to
pre-select items. I think the problem lies with this line of code:
TheList1.List(i - 1).Selected = True
This should get executed if the item currently being added to the list from
the master list is contained in the "currentlist". It's possible that the
index isn't right but am not sure.

Thanks,
MikeZz


Private Sub UserForm_Initialize()

Dim i, c, r, r1, c1
Dim AddThis
Dim defR, defC
Dim It

errToMany = False
TheList1.Clear
tempCurr = currentList

defR = UBound(currentList, 1) - LBound(currentList, 1) + 1
defC = UBound(currentList, 2) - LBound(currentList, 2) + 1

For i = LBound(buildList, 1) To UBound(buildList, 1)
AddThis = True
If (listType = "Company" And buildList(i) = CompanyName) Or _
buildList(i) = "Other" Or buildList(i) = "OT" Then
AddThis = False
End If
If AddThis = True Then
TheList1.AddItem buildList(i): c = TheList1.ListCount
For r1 = 1 To defR
For c1 = 1 To defC
' It = TheList1.List(TheList1.ListCount)
If currentList(r1, c1) = buildList(i) Then
' TheList1.List(i - 1).Selected = True
GoTo gotonextItem
End If
Next c1
Next r1
gotonextItem:
End If

Next i

TheList1.AddItem "Other"
Qty.Caption = 0
Qty2.Caption = TheList1.ListCount

FormTitle.Caption = "Primary Co: " & CompanyName
FormTitle.Visible = True


End Sub

 

[Joel]

Make sure you have the option for your control set properly. The default is
xlNone

see VBA help MultiSelect Property

lb.ControlFormat.MultiSelect = xlSimple


Remarks
Single select (xlNone) allows only one item at a time to be selected.
Clicking the mouse or pressing the SPACEBAR cancels the selection and selects
the clicked item.

Simple multiselect (xlSimple) toggles the selection on an item in the list
when click it with the mouse or press the SPACEBAR when the focus is on the
item. This mode is appropriate for pick lists, in which there are often
multiple items selected.

Extended multiselect (xlExtended) usually acts like a single-selection list
box, so when you click an item, you cancel all other selections. When you
hold down SHIFT while clicking the mouse or pressing an arrow key, you select
items sequentially from the current item. When you hold down CTRL while
clicking the mouse, you add single items to the list. This mode is
appropriate when multiple items are allowed but not often used.

You can use the Value or ListIndex property to return and set the selected
item in a single-select list box.

You cannot link multiselect list boxes by using the LinkedCell property.

Example
This example creates a simple multiselect list box.

Set lb = Worksheets(1).Shapes.AddFormControl(xlListBox, _
Left:=10, Top:=10, Height:=100, Width:100)
lb.ControlFormat.MultiSelect = xlSimple

 

[MikeZz]

I think I figured out the problem.
I set the code up to put "Other" at the end of the list just in case it fell
in the middel on the master list.

I had skipped "Other" until after other items were added but I didn't have a
seperate counter of actual items added to the list. So when I skipped it,
the list didn't grow (say only 3 long) but I tried to set the selected
property as if it were added.... so an index issue.

Thanks anyway.... it how works like a charm.

Here's the finished code......

Private Sub UserForm_Initialize()

Dim i, c, r, r1, c1, why
Dim AddThis
Dim defR, defC
Dim It
Dim items

On Error GoTo ErrHandler
init = True

errToMany = False
TheList1.Clear
tempCurr = currentList

defR = UBound(currentList, 1) - LBound(currentList, 1) + 1
defC = UBound(currentList, 2) - LBound(currentList, 2) + 1

items = 0
For i = LBound(buildList, 1) To UBound(buildList, 1)
AddThis = True
If (listType = "Company" And buildList(i) = CompanyName) Or _
buildList(i) = "Other" Or buildList(i) = "OT" Then
AddThis = False
End If

If AddThis = True Then
items = items + 1
TheList1.AddItem buildList(i): c = TheList1.ListCount
If IsInVariantArray(buildList(i), currentList, True) Then
TheList1.Selected(items - 1) = True
GoTo gotonextItem
End If

gotonextItem:
End If

Next i

TheList1.AddItem "Other": items = items + 1

If IsInVariantArray("Other", currentList, True) Then
TheList1.Selected(items - 1) = True
End If


Qty.Caption = Selected_Count(TheList1)
Qty2.Caption = TheList1.ListCount

FormTitle.Caption = "Primary Co: " & CompanyName
FormTitle.Visible = True


init = False

Exit Sub

ErrHandler:
why = MsgBox("Err Number: " & Err.Number & Chr(13) & _
"Err Number: " & Err.Number & Chr(13) & _
"Build List: " & buildList(i) & Chr(13), vbCritical)

Resume Next
End Sub