CODEWITHSUNDEEP
vbaexcellistbox
user3175856
user3175856

Reputation: 29

How to display Values from a multiselect listbox

I have a form in Excel macro. This form will capture the values inserted in textboxes, listbox and store in Sheet2.

There are 2 buttons in the form applet named "Next" and "Previous". These button will be used for navigating between the saved records. I am able to navigate between records and the values display fine in textboxes. However, I am not sure how can I display the Values from listboxes. My list box is a multiselect list box.

I have provided snippet of my code on how the records are saved in sheet2 and how the navigation happens when clicked on Next button. 

     Private Sub Save_Click()

      Dim ctl As Control
      Dim S As String
      Dim i As Integer


      RowCount = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count
     With Worksheets("Sheet2").Range("A1")
    .Offset(RowCount, 0).Value = Me.Name1.Value  ' capture value from list box

     'below code is for capturing value from multiselect listbox 
     With AOI
      For i = 0 To .ListCount - 1
         If .Selected(i) = True Then S = S & ", " & .List(i)
     Next i
      Range("A1").Offset(RowCount, 10).Value = S
     End With
    End Sub

Below code is for navigating between saved records.. 

   Private Sub Next1_Click()

      strCurrentSetofRows = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count
      i = i + 1: j = 0

      If i > (strCurrentSetofRows - 1) Then
        MsgBox "No More Records"
      Exit Sub
      End If

      Set sRange = Worksheets("Sheet2").Range("A1")

      Name1.Text = sRange.Offset(i, j).Value: j = j + 1
   End Sub

Any thoughts on how can I display saved values of AOI (my field).

Upvotes: 1

Views: 12176

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Since you are storing the values using , as a separator, you can use the same to split the values and upload it to the ListBox. BTW, I hope you are generating the ListBox with the complete list in the UserForm's Initialize event?

Here is a very basic example. Please amend it to suit your needs.

Let's say Cell A1 has Blah1,Blah2,Blah6. Then try this code

Option Explicit

Dim i As Long, j As Long

Private Sub UserForm_Initialize()
    ListBox1.MultiSelect = fmMultiSelectMulti

    For i = 1 To 10
        ListBox1.AddItem "Blah" & i
    Next
End Sub

Private Sub CommandButton1_Click()
    Dim ArValues As Variant
    Dim sValue As String
    Dim multivalues As Boolean

    If InStr(1, Range("A1").Value, ",") Then
        ArValues = Split(Range("A1").Value, ",")
        multivalues = True
    Else
        sValue = Range("A1").Value
        multivalues = False
    End If

    If multivalues = True Then
        For i = 0 To UBound(ArValues)
            For j = 0 To ListBox1.ListCount - 1
                If ListBox1.List(j) = ArValues(i) Then
                    ListBox1.Selected(j) = True
                    Exit For
                End If
            Next j
        Next i
    Else
        For j = 0 To ListBox1.ListCount - 1
            If ListBox1.List(j) = sValue Then
                ListBox1.Selected(j) = True
                Exit For
            End If
        Next j
    End If
End Sub

Screenshot

enter image description here

Upvotes: 1

Related Questions