How do I get a user form to recall list box selections from a worksheet?

[NorthbyNorthwest]

Hi, everyone. Hope someone can help me finish a project I am working on. It is a user form to control data entry. The actual worksheet is hidden by code; so, users can only input, update, and delete records through the user form. Thanks to a lot of internet help including this message board I have been successful in the past. My earlier user forms were a combination of text and combo boxes. The user form I am now working on has text boxes, combo boxes, and 20 multiselect list boxes. The user can add data from the user form to the next empty row in worksheet. My problem is when the user would need to update a record.

I know how to find the row in question and how to return text and combo box values to the user form from the worksheet. I do not know how to do the same with the multiselect list boxes. I found code to write the selected list box values to a single cell and even insert a comma as a delimiter. Example: I have a cell that reads “201, 203, 207.” Each is a selection from Listbox1. For users to update this record, I need to pass these selections back to the user form. How do I get user form to understand that it should tick these three items in Listbox1?

 

[6StringJazzer]

First you need to have a way loop through the items in the cell, then you have to compare each of those to each item in the list. Something like this (but I obviously can't write code specific to your file without your file):

   Dim A As Variant
   Dim S As Variant
   Dim LI As Long
   
   A = Split(Cells(R, C), ",") ' where Cells(R, C) is the cell containing the list of values
   
   For LI = 0 To ListBox1.ListCount - 1
      ListBox1.Selected(LI) = False
   Next LI
   
   For Each S In A
      For LI = 0 To ListBox1.ListCount - 1
         If S = ListBox1.List(LI) Then
            ListBox1.Selected(LI) = True
         End If
      Next LI
   Next S

 

[NorthbyNorthwest]

First you need to have a way loop through the items in the cell, then you have to compare each of those to each item in the list. Something like this (but I obviously can't write code specific to your file without your file):

   Dim A As Variant
   Dim S As Variant
   Dim LI As Long
  
   A = Split(Cells(R, C), ",") ' where Cells(R, C) is the cell containing the list of values
  
   For LI = 0 To ListBox1.ListCount - 1
      ListBox1.Selected(LI) = False
   Next LI
  
   For Each S In A
      For LI = 0 To ListBox1.ListCount - 1
         If S = ListBox1.List(LI) Then
            ListBox1.Selected(LI) = True
         End If
      Next LI
   Next S

Hi, 6StringJazzer. Ecstatic to hear from you. Thanks for responding. I appreciate you explaining how to approach the matter. I thought I understood and gave the code a try. But, unfortunately, it did not produced desired result. Not only did it not select the list box items in the user form, it emptied the cell contents in the worksheet. Not sure where to go from here.

 

[Corbett]

You gave an example: I have a cell that reads “201, 203, 207.” Does it really contain a space after the commas and a full stop at the end? If so you need to adapt 6StringJazzers code to strip out the spaces and full stop before comparing with the listbox items.

 

[NorthbyNorthwest]

You gave an example: I have a cell that reads “201, 203, 207.” Does it really contain a space after the commas and a full stop at the end? If so you need to adapt 6StringJazzers code to strip out the spaces and full stop before comparing with the listbox items.

Hi, Corbett. I tried both ways: "201, 203, 207" and "201,203,207." Would it make a difference if my list items were two columns? The first column contains numbers. The second column contains the narrative explanation of the number. I only copy the first column value(s) (column 0) to the worksheet cell.

 

[Corbett]

Hard to know without looking at the sheet. Can you upload it?

 

[6StringJazzer]

Not only did it not select the list box items in the user form, it emptied the cell contents in the worksheet.

This makes no sense at all. The code I provided cannot possibly change the content of the cell. If you are having a problem after implementing code someone else provided then you should show exactly what code you actually used.

 

[NorthbyNorthwest]

This makes no sense at all. The code I provided cannot possibly change the content of the cell. If you are having a problem after implementing code someone else provided then you should show exactly what code you actually used.

 

[6StringJazzer]

Did you mean to add anything to my quoted message?

 

[NorthbyNorthwest]

Hi, 6StringJazzer. See code I used below. I subbed out the R and C for my own row and column references. I also subbed out the listbox reference for my own. Did I do something wrong?

Dim A As Variant
   Dim S As Variant
   Dim LI As Long
   
   A = Split(Cells(9, 38), ",") ' where Cells(R, C) is the cell containing the list of values
   
   For LI = 0 To lbxList1.ListCount - 1
      lbxList1.Selected(LI) = False
   Next LI
   
   For Each S In A
      For LI = 0 To lbxList1.ListCount - 1
         If S = lbxList1.List(LI) Then
            lbxList1.Selected(LI) = True
         End If
      Next LI
   Next S

Below is the entire sub routine.

Private Sub cmdFind_Click()
Dim SearchValue(1 To 2) As Variant
Dim firstaddress As String
Dim FoundMatch As Boolean
Dim c As Range

'Find both employee name and the audit number to recall a record
SearchValue(1) = Me.txtEmployee.Value
SearchValue(2) = Val(Me.txtAuditNumber.Value)

With ThisWorkbook.Worksheets("Audit Details").Columns(2)
Set c = .Find(SearchValue(1), LookIn:=xlFormulas, lookat:=xlWhole)
If Not c Is Nothing Then
firstaddress = c.Address
Do
FoundMatch = CBool(SearchValue(2) = c.Offset(, 7).Value)

If FoundMatch Then
'Write employee personal data from active worksheet to userform controls, page 1 text and combo boxes
Cells(9, 2).Select
txtEmployee = ActiveCell.Value
txtPosition.Value = ActiveCell.Offset(0, 1)
cboReviewStatus.Value = ActiveCell.Offset(0, 2)
txtUnit.Value = ActiveCell.Offset(0, 3)
txtSupervisor.Value = ActiveCell.Offset(0, 4)
cboMonth.Value = ActiveCell.Offset(0, 5)
cboAuditGrp.Value = ActiveCell.Offset(0, 6)
txtAuditNumber.Value = ActiveCell.Offset(0, 7)
txtDateAudited.Value = ActiveCell.Offset(0, 8)
txtDateWorked.Value = ActiveCell.Offset(0, 9)
txtVetSurname.Value = ActiveCell.Offset(0, 10)
txtFileNumber.Text = ActiveCell.Offset(0, 11)
txtID.Value = ActiveCell.Offset(0, 12)
txtPageCount.Value = ActiveCell.Offset(0, 13)

'Write checklist answers from worksheet to option buttons, pages 2 - 6 option buttons 1 -66
If ActiveCell.Offset(0, 14).Value = "Y" Then
optButton1 = True
ElseIf ActiveCell.Offset(0, 14).Value = "N" Then
OptButton2 = True
Else
optButton3 = True
End If


If ActiveCell.Offset(0, 15).Value = "Y" Then
optButton4 = True
ElseIf ActiveCell.Offset(0, 15).Value = "N" Then
optButton5 = True
Else
optButton6 = True
End If


If ActiveCell.Offset(0, 16).Value = "Y" Then
optButton7 = True
ElseIf ActiveCell.Offset(0, 16).Value = "N" Then
optButton8 = True
Else
optButton9 = True
End If


If ActiveCell.Offset(0, 17).Value = "Y" Then
optButton10 = True
ElseIf ActiveCell.Offset(0, 17).Value = "N" Then
optButton11 = True
Else
optButton12 = True
End If


If ActiveCell.Offset(0, 18).Value = "Y" Then
optButton13 = True
ElseIf ActiveCell.Offset(0, 18).Value = "N" Then
optButton14 = True
Else
optButton15 = True
End If


If ActiveCell.Offset(0, 19).Value = "Y" Then
optButton16 = True
ElseIf ActiveCell.Offset(0, 19).Value = "N" Then
optButton17 = True
Else
optButton18 = True
End If


If ActiveCell.Offset(0, 20).Value = "Y" Then
optButton19 = True
ElseIf ActiveCell.Offset(0, 20).Value = "N" Then
optButton20 = True
Else
optButton21 = True
End If


If ActiveCell.Offset(0, 21).Value = "Y" Then
optButton22 = True
ElseIf ActiveCell.Offset(0, 21).Value = "N" Then
optButton23 = True
Else
optButton24 = True
End If


If ActiveCell.Offset(0, 22).Value = "Y" Then
optButton25 = True
ElseIf ActiveCell.Offset(0, 22).Value = "N" Then
optButton26 = True
Else
optButton27 = True
End If


If ActiveCell.Offset(0, 23).Value = "Y" Then
optButton28 = True
ElseIf ActiveCell.Offset(0, 23).Value = "N" Then
optButton29 = True
Else
optButton30 = True
End If


If ActiveCell.Offset(0, 24).Value = "Y" Then
optButton31 = True
ElseIf ActiveCell.Offset(0, 24).Value = "N" Then
optButton32 = True
Else
optButton33 = True
End If


If ActiveCell.Offset(0, 25).Value = "Y" Then
optButton34 = True
ElseIf ActiveCell.Offset(0, 25).Value = "N" Then
optButton35 = True
Else
optButton36 = True
End If


If ActiveCell.Offset(0, 26).Value = "Y" Then
optButton37 = True
ElseIf ActiveCell.Offset(0, 26).Value = "N" Then
optButton38 = True
Else
optButton39 = True
End If


If ActiveCell.Offset(0, 27).Value = "Y" Then
optButton40 = True
ElseIf ActiveCell.Offset(0, 27).Value = "N" Then
optButton41 = True
Else
optButton42 = True
End If


If ActiveCell.Offset(0, 28).Value = "Y" Then
optButton43 = True
ElseIf ActiveCell.Offset(0, 28).Value = "N" Then
optButton44 = True
Else
optButton45 = True
End If


If ActiveCell.Offset(0, 29).Value = "Y" Then
optButton46 = True
ElseIf ActiveCell.Offset(0, 29).Value = "N" Then
optButton47 = True
Else
optButton48 = True
End If


If ActiveCell.Offset(0, 30).Value = "Y" Then
optButton49 = True
ElseIf ActiveCell.Offset(0, 30).Value = "N" Then
optButton50 = True
Else
optButton51 = True
End If


If ActiveCell.Offset(0, 31).Value = "Y" Then
optButton52 = True
ElseIf ActiveCell.Offset(0, 31).Value = "N" Then
optButton53 = True
Else
optButton54 = True
End If


If ActiveCell.Offset(0, 32).Value = "Y" Then
optButton55 = True
ElseIf ActiveCell.Offset(0, 32).Value = "N" Then
optButton56 = True
Else
optButton57 = True
End If


If ActiveCell.Offset(0, 33).Value = "Y" Then
optButton58 = True
ElseIf ActiveCell.Offset(0, 33).Value = "N" Then
optButton59 = True
Else
optButton60 = True
End If


If ActiveCell.Offset(0, 34).Value = "Y" Then
optButton61 = True
ElseIf ActiveCell.Offset(0, 34).Value = "N" Then
optButton62 = True
Else
optButton63 = True
End If


If ActiveCell.Offset(0, 35).Value = "Y" Then
optButton64 = True
ElseIf ActiveCell.Offset(0, 35).Value = "N" Then
optButton65 = True
Else
optButton66 = True
End If


'Write listbox values from worksheet to listbox controls, pages 2 - 6 listboxes 1 - 21
'Test code below from Mr Excel forum on first listbox
Dim A As Variant
Dim S As Variant
Dim LI As Long

A = Split(Cells(9, 38), ",") ' where Cells(R, C) is the cell containing the list of values

For LI = 0 To lbxList1.ListCount - 1
lbxList1.Selected(LI) = False
Next LI

For Each S In A
For LI = 0 To lbxList1.ListCount - 1
If S = lbxList1.List(LI) Then
lbxList1.Selected(LI) = True
End If
Next LI
Next S


'ActiveCell.Offset(0, 58) = txtNotes.Value
'ActiveCell.Offset(0, 59) = txtQAS.Value

Else
MsgBox "Record not Found"
End If
Set c = .FindNext(c)
If c Is Nothing Then GoTo DoneFinding
Loop While c.Address <> firstaddress
End If
End With
End Sub