locked
Select values in List box from a cell value RRS feed

  • Question

  • Hi All,

    How to tickmark the values in excel multi select list box using a cell value?

    I have a list box and I have made it as multi select, whenever user click on button(event) the list box appears and the values which user will select will be inserted in a cell.(comma seperated)

    Now if user again click on button then if that cell is already having values then I the list box should display with automatically those values should be tick marked then the user can untick the value or select another one that I have handled.

    Hope you understood my problem.

    Appreciate the quick response.

    Thanks

    Sumit


    Please mark it as helpful if it helps. Thanks Sumit BI - Integration Services

    • Changed type Siddharth Rout Tuesday, March 26, 2013 1:36 PM This is Question and not a discussion
    Monday, March 25, 2013 2:03 PM

Answers

  • Change the ranges to the sheet and address of the cell of interest. I wrote this to use two buttons, one button to load values and the other to write the values, in case multiple write requests are needed.

    Dim rngC As Range
    
    Private Sub CommandButton1_Click()
        'load the values
        Dim v As Variant
        Dim i As Integer
        Dim j As Integer
    
        Set rngC = Worksheets("Sheet1").Range("C2")
    
        If rngC.Value <> "" Then
            v = Split(rngC.Value, ",")
    
            For i = LBound(v) To UBound(v)
                For j = 0 To Me.ListBox1.ListCount - 1
                    If Me.ListBox1.List(j) = Trim(v(i)) Then
                        Me.ListBox1.Selected(j) = True
                    End If
                Next j
            Next i
        End If
    End Sub
    
    Private Sub CommandButton2_Click()
        'write the values
        Dim j As Integer
    
        Set rngC = Worksheets("Sheet1").Range("C2")
    
        Application.EnableEvents = False
        rngC.Value = ""
        For j = 0 To Me.ListBox1.ListCount - 1
            If Me.ListBox1.Selected(j) = True Then
                If rngC.Value = "" Then
                    rngC.Value = Me.ListBox1.List(j)
                Else
                    rngC.Value = rngC.Value & ", " & Me.ListBox1.List(j)
                End If
            End If
        Next j
        Application.EnableEvents = False
    
    End Sub


    • Edited by Siddharth Rout Tuesday, March 26, 2013 1:28 PM Indented Code
    • Proposed as answer by Siddharth Rout Tuesday, March 26, 2013 1:36 PM
    • Marked as answer by Dummy yoyo Thursday, March 28, 2013 9:21 AM
    Monday, March 25, 2013 2:52 PM

All replies

  • Change the ranges to the sheet and address of the cell of interest. I wrote this to use two buttons, one button to load values and the other to write the values, in case multiple write requests are needed.

    Dim rngC As Range
    
    Private Sub CommandButton1_Click()
        'load the values
        Dim v As Variant
        Dim i As Integer
        Dim j As Integer
    
        Set rngC = Worksheets("Sheet1").Range("C2")
    
        If rngC.Value <> "" Then
            v = Split(rngC.Value, ",")
    
            For i = LBound(v) To UBound(v)
                For j = 0 To Me.ListBox1.ListCount - 1
                    If Me.ListBox1.List(j) = Trim(v(i)) Then
                        Me.ListBox1.Selected(j) = True
                    End If
                Next j
            Next i
        End If
    End Sub
    
    Private Sub CommandButton2_Click()
        'write the values
        Dim j As Integer
    
        Set rngC = Worksheets("Sheet1").Range("C2")
    
        Application.EnableEvents = False
        rngC.Value = ""
        For j = 0 To Me.ListBox1.ListCount - 1
            If Me.ListBox1.Selected(j) = True Then
                If rngC.Value = "" Then
                    rngC.Value = Me.ListBox1.List(j)
                Else
                    rngC.Value = rngC.Value & ", " & Me.ListBox1.List(j)
                End If
            End If
        Next j
        Application.EnableEvents = False
    
    End Sub


    • Edited by Siddharth Rout Tuesday, March 26, 2013 1:28 PM Indented Code
    • Proposed as answer by Siddharth Rout Tuesday, March 26, 2013 1:36 PM
    • Marked as answer by Dummy yoyo Thursday, March 28, 2013 9:21 AM
    Monday, March 25, 2013 2:52 PM
  • Wooo....it worked and exactly what I was looking for....Thanks Bernie :)

    Please mark it as helpful if it helps. Thanks Sumit BI - Integration Services

    Tuesday, March 26, 2013 9:31 AM