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?
CodePudding user response:
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