VBA - Preselecting Userform Listbox values matched from a string separated by commas

Question

1) I have a column of data which fills a multiselect listbox in a userform. Values are the days of the week "Monday" - "Sunday"

Dim ListsSheet As Worksheet
Dim LastRow As Long
Dim aCell As Range

Set ListsSheet = Sheets("Lists")
With ListsSheet
    'Fills Days
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For Each aCell In .Range("A2:A" & LastRow)
        If aCell.value <> "" Then
        Me.Day.AddItem aCell.value
        End If
    Next

2) I'm able to gather the multi-selected values and transfer it to a worksheet for manipulation. Assuming the user selected days "Tuesday" + "Wednesday", my data in the cells = "Tuesday, Wednesday"

3) I'm attempting to recreate the listbox as it was originally selected by a user. I am able to use .additem to generate the list of days again, but I'm unsure how to go about selecting the correct .listindex value based on the initial selection

Variatus · Accepted Answer · 2017-04-08 05:34:39Z

1

The selection you make in a listbox are recorded in a zero-based boolean array which has as many elements as the listbox has items. This array is accessible by the listbox's Read/Write Selected property. In your case ListBox1.Selected(0) refers to "Monday" and ListBox1.Selected(6) to Sunday.

ListBox1.Selected(0) will be True or False depending upon whether Monday is selected or not. You can read this property when you close the form and re-establish the same setting by setting ListBox1.Selected(0) = True (or False) when you show the form again.

Since you have the weekday names in A2:A8 you might write the Selected() array into B2:B8 in real time using the ListBox1_Change event, and use this range both on your sheet and to reset the ListBox.

By the way, your way of setting the list isn't the most efficient. For one, you always want 7 items in your list. Therefore

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

will cause more mistakes than it prevents. For another, Excel's VBA has provided the Rowsource property. You can set the list by setting that property. Both the following examples are correct.

ListBox1.RowSource = ActiveSheet.Range("A2:A8").Address
ListBox1.RowSource = "DaysList"     ' where DaysList is a named range

The use of ActiveSheet is safe if you call the form from a worksheet and therefore know which one will be active at the time. Or you can apply an array directly to the List property, like this:-

ListBox1.List = Split("Mon Tue Wed Thu Fri Sat Sun", " ")

answered Apr 8, 2017 at 5:34

Variatus

14.4k2 gold badges16 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jazzmasterflex Over a year ago

Thanks! That helps a lot! To clarify my question further though, My data = "Mon, Tues, Fri" and I've used split to separate it. I'm trying to present a listbox that shows all the days, and then the predetermined days (mon, tues, fri) to be selected. would I need to run a match / find / for each of the listindex(I)? what would that look like?

Jazzmasterflex Over a year ago

Figured it out: ['code'] For i = 1 To day.ListCount If InStr(1, Sheets("test").Range("M2").Text, (day.List(i - 1))) > 0 Then day.Selected(i - 1) = True End If Next['code']

Collectives™ on Stack Overflow

VBA - Preselecting Userform Listbox values matched from a string separated by commas

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related