Search code examples
excelvbalistboxuserform

VBA: Default selected listbox value from a cell

I've looked high and low for an explanation of this and I really am at my wits end, I don't even know if I'm asking the right question because I can't find any answers.

I have a userform where there are 2 modes: NEW and EDIT

When someone enters a NEW entry, there is a listbox field that allows multiple entries. This information gets concatenated into a single cell of the worksheet separated by commas.

When the form is in EDIT mode, it retrieves the information from the worksheet and populates the form with the existing row details based on an ID number. I can't seem to figure out how to get the list box to pull the selection from the worksheet back into the userform in EDIT mode

On clicking edit button to pull details from sheet into form:

Dim DataID as String
DataID = Trim(txt_RetrieveID.Text) Sheets("Lists").Range("I2").Value = DataID
lastrow = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row

For I=3 to lastrow
If Worksheets("Data").Cells(i, 1).Value = DataID then
txt_date.Text=Sheets("Data").Cells(i,4).Value
''''etc for all the different fields

I was thinking that I'd have to split the concatenated field

Dim GroupValue as String
GroupValue = Sheets("Lists").Range("J2").Value
'"J2" is a fixed point where the list of items populates for the referenced record
Dim SingleValue() As String
SingleValue = Split(GroupValue, ", ")
Next

but I can't figure out how to bring those values back in as the default selection on the listbox in EDIT mode.

How can I take these multiple items and have them highlight as the selected value in EDIT mode?

Solution

  • this is an example that uses the Dictionary object to accomplish what you are asking

    Dim dict As Scripting.Dictionary  ' add microsoft scripting runtime to your tools/references

Private Sub CommandButton1_Click()
        Dim x As Integer, y As Integer, Key As Variant
        'clear prior selections in listbox1
        ListBox1.MultiSelect = fmMultiSelectSingle
        ListBox1.MultiSelect = fmMultiSelectMulti
        
        y = 24 ' your "record selection" is done somehow
        For x = 4 To 20 ' referencing your data values from the sheet, set as needed
                If Cells(y, x) = "" Then Exit For
                Key = Cells(y, x) ' must be a variant to read the key's value
                ListBox1.Selected(dict(Key)) = True
        Next x
End Sub

Private Sub UserForm_Initialize()
        Dim x As Integer
        Set dict = New Scripting.Dictionary
        ListBox1.List = Split("Item3,Item2,Item0,Iterm8,Item10,Item44,Item09,Item23,Item11,item1,item12,item9,item31", ",")
        'after the listbox is populated, build your dictionary
        With ListBox1
                For x = 0 To .ListCount - 1
                        dict.Add .List(x), x
                 Next x
        End With
 End Sub