Pages

Saturday, February 9, 2013

Populate form control combo box using VBA

If you want to populate form control combo box  or get selected value using VBA . Try this macro -


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

Sub method2()
 'change combobox name and sheet here
    With Sheet1.Shapes("drp_down1").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 combobox name and sheet here
     With Sheet1.Shapes("drp_down1").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 combobox name and sheet here
     With Sheet1.Shapes("drp_down1").ControlFormat
        .RemoveAllItems
            For i = LBound(arr) To UBound(arr)
                .AddItem arr(i, 1)
            Next
     End With

End Sub

Macro to get selected value


Sub selected_value()
    With Sheet1.Shapes("drp_down1").ControlFormat
        MsgBox .List(.ListIndex)
    End With
End Sub





No comments:

Post a Comment