Pages

Saturday, February 9, 2013

Populate form control list box using VBA

If you want to populate form control list box using VBA . Try this macro -



Sub method1()
    'change listbox name and sheet here
    With Sheet1.Shapes("lst_box1").ControlFormat
        .RemoveAllItems
        .AddItem "Jan"
        .AddItem "Feb"
        .AddItem "Mar"
    End With
End Sub



Sub method2()
    'change listbox name and sheet here
    With Sheet1.Shapes("lst_box1").ControlFormat
            'change fill range here
            .ListFillRange = "Sheet1!$a$1:$a$5"
    End With
End Sub



Sub method3()

    Dim rng As Range, cl As Range
    'change fill range here
    Set rng = Sheet1.Range("a1:a5")
    'change listbox name and sheet here
     With Sheet1.Shapes("lst_box1").ControlFormat
        .RemoveAllItems
            For Each cl In rng
                .AddItem cl.Value
            Next
     End With

End Sub



Sub method4()

    Dim arr, i As Long
    'change fill range here
    arr = Sheet1.Range("a1:a5")
    'change listbox name and sheet here
     With Sheet1.Shapes("lst_box1").ControlFormat
        .RemoveAllItems
            For i = LBound(arr) To UBound(arr)
                .AddItem arr(i, 1)
            Next
     End With

End Sub




Macro to get all Selected Item/Values



Sub selected_items()

    Dim result1 As String
    
    
    With Sheet1.Shapes("lst_box1").OLEFormat.Object
        For i = 1 To .ListCount
            If .Selected(i) Then
                'Concatenate selected values
                result1 = result1 & ";" & .List(i)
            End If
        Next
    End With
    
    If result1 <> "" Then result1 = VBA.Right(result1, Len(result1) - 1)
    MsgBox result1
    
End Sub

5 comments:

  1. Is lst_box1 the name of the actual list box you implemented? If so, how did you come up with this name for the list box? Is there any way to change this list box name if the list box is form control?

    ReplyDelete
  2. https://lh6.googleusercontent.com/-kHWbfl5EUwg/UhT7P9-3kxI/AAAAAAAACmc/akpdKLKi_gg/w987-h417-no/solution+snapshot.png

    ReplyDelete
  3. View this link . As soon as you select the listbox . You can see the name thr

    ReplyDelete
  4. Thanks so much! You're definitely a huge help to those new to VBA such as myself.

    ReplyDelete