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

Download Working File

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