Copy Listbox Selections from a Worksheet Back to the Userform

[NorthbyNorthwest]

Hi, everyone. I have a multipage form with a variety of controls. This includes several multiselect list boxes. The list boxes have two columns. The first column has number, the second a narrative. When end users make one or more selections from a list box the first column of the selections are copied to a worksheet. The code encloses numbers in brackets, for example if multiple selections were made the worksheet cell might read: [201][217][224]. If no selections then a hyphen is copied to the worksheet. (It’s a placeholder. I didn’t want to leave cells blank.) I have the following code to accomplish this task. Note: Button 1 is a “yes”. Button 3 is a “N/A.” Button 2 (else in the code below) is the “No” button.

If optButton1 = True Or optButton3 = True Then
        ActiveCell.Offset(0, 55).value = "-"
    Else
        myVar = ""
        For X = 0 To Me.lbxList1.ListCount - 1
            If Me.lbxList1.Selected(X) Then
            If myVar = "" Then
          myVar = "[" & Me.lbxList1.List(X, 0) & "]"
        Else
            myVar = myVar & "[" & Me.lbxList1.List(X, 0) & "]"
            End If
        End If
        Next X
        ActiveCell.Offset(0, 55).value = myVar
    End If

My problem. The form is designed to both add data to a worksheet and update data in a worksheet. So, when end users search for a record with user form the data is copied/written/read back to the form controls. I can do this other controls (textboxes, comboboxes, option buttons). But not the listboxes. Is there any way to reverse engineer the code above? If no, can some suggest something else? As it stands now, when the end user updates an entry, they have to go through process of reselecting listbox item(s) because I cannot write the values back to the form.

 

[DanteAmor]

Try to adapt the following, the confusing thing is to know what is the ActiveCell, but following your code, if the active cell for you is the correct one, then 55 cells to the right should be the string of numbers, for example [201][217][224], so with the following , the numbers are obtained, those numbers are searched in the listbox and the item is selected in the listbox.

'reverse engineer
  Dim myVar As Variant, num As Variant
  Dim i As Long
  
  With ActiveCell.Offset(0, 55)
    If .Value = "-" Then
      'The code when it is hyphen
    Else
      myVar = Replace(.Value, "[", "")
      With lbxList1
        For Each num In Split(myVar, "]")
          For i = 0 To .ListCount - 1
            If .List(i, 0) = Val(num) Then
              .Selected(i) = True
            End If
          Next
        Next
      End With
    End If
  End With

 

[NorthbyNorthwest]

Dante, you are fantastic! You made my day! This is a project I've been working on for quite a while. Thank you. I also want to send a big shout out to the rest of the forum. Several of you have helped me on this project. I've said it before and I'll say it again, this forum is awesome: the talent and more the importantly, the people who are so generous with their talent.