PDA

View Full Version : [SOLVED] Listbox Multiselect issue



mdmackillop
03-21-2017, 11:09 AM
I've an ActiveX ListBox on a sheet with ListFillRange referring to a named range on sheet 2. Having selected some items, the selection is cleared if any cells are populated or cleared on either sheet. Single select listboxes are not affected. (Excel 2010)

SamT
03-21-2017, 05:07 PM
Can you save the selection (on UserForm_Deactivate) and "reselect" them on UF_Activate?

Paul_Hossler
03-21-2017, 06:23 PM
Set calculation to manual, or don't use a formula in the named range refers to (e.g. =Sheet2!$A$2:$A$13)

rlv
03-21-2017, 11:18 PM
Can you code it to preserve the selections?


Private Sub CommandButton2_Click()
Dim a, C, d
Dim B()
Dim SelItem() As Integer
Dim i, X


X = 0
For i = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(i) Then
ReDim Preserve B(X)
ReDim Preserve SelItem(X)
B(X) = ListBox2.List(i)
SelItem(X) = i
X = X + 1
End If
Next


For i = 0 To X - 1
Cells(20, 2).Offset(i) = B(i)
Next i

For i = 0 To X - 1
ListBox2.Selected(SelItem(i)) = True
Next i
End Sub

mdmackillop
03-22-2017, 02:30 AM
Thanks both


don't use a formula in the named range refers to
This guided me in the right direction. I wany to keep the source dynamic, but I can fill the values and remove the ListFillRange.
Regards
MD



Private Sub Worksheet_Activate()
Me.ListBox2.List = Sheets(2).Range("Form").Value
End Sub

snb
03-22-2017, 04:12 AM
Named ranges are meant for Excel's User Interface.
If you apply VBA it is redundant (and unnecessarily recalculates too often).
Generally speaking: always avoid listfillrange or rowsource to populate a listbox/combobox.

Since the listbox has to be filled only once I'd prefer:


Private Sub Workbook_Open()
Sheet1.ListBox1.List = Sheet2.Cells(1).CurrentRegion.Value
End Sub

mdmackillop
03-22-2017, 04:30 AM
Thanks SNB

That makes sense.

Paul_Hossler
03-22-2017, 05:28 AM
I think the Worksheet_Activate event would be better, just in case the list source range changed (expanded/shrunk) after the WB was opened

snb
03-22-2017, 05:34 AM
In that case you'd better use the worksheet_change event.

Paul_Hossler
03-22-2017, 07:31 AM
In that case you'd better use the worksheet_change event.

I was thinking that since the data list is on Sheet2 but the control is on Sheet1, it'd be better to only update the control list range when you activated to Sheet1

That way there's no cycles wasted by just changing the data on Sheet1 or Sheet2

mdmackillop
03-22-2017, 09:27 AM
Lots to think about.
I'm going with the Worksheet Change event on Sheet 2. The Lists will be updated when the raw data changes.