CODEWITHSUNDEEP
vbaexcel
Gek
Gek

Reputation: 43

Passing Cell Values into a combo box list

I am looking to pass cells values into the list for a combo box in Excel.

I have created an array to pass the cell values into but they aren't getting to the combo box list when they are being called. I need the cell values to display as the drop down items and all that displays is the blank space:

Sub Clear_Invoice_Data()

    Dim Arr As Variant
    Arr = Range("A1:A5").Value

    frmAddLineItem.ddlProduct.List = Arr    

End Sub

It is called by a button from a User form:

Private Sub cmdClearAll_Click()

    Call Button_Functions.Clear_Invoice_Data
    i = 18

End Sub

Pictures of problem: (no values)

no elements 5 elements

Upvotes: 1

Views: 2975

Answers (3)

Dolcekar
Dolcekar

Reputation: 39

I am just getting into VBA and I am doing exactly this. I think the way I found is pretty easy and straightforward. I'll share it in case it can help someone

  1. Declare variable to store table from Worksheet as "range". Make sure it's outside of sub routines so it can be called by any function

Dim myTable As Range

  1. Store table values in declared variable. Then select which column (range) of values you want to store in your combo box.
Private Sub UserForm_Initialize()
Set myTable = Worksheets("dataSheetName").Range("A2:B6")
    Me.myComboBox.List = myTable.Columns(1).Value
End Sub ```

Upvotes: 0

Paresh J
Paresh J

Reputation: 2419

Using ComboBox, you can use .List property of the same to populate worksheet values to combox box.

Check this below example:

ComboBox1.List = Worksheets("Sheet1").Range("A1:A5").Value

If you want to use Array, 

Dim Arr As Variant
Arr = Worksheets("Sheet1").Range("A1:A5").Value
ComboBox1.List = Arr

Upvotes: 1

Chrismas007
Chrismas007

Reputation: 6105

There is another (rougher) method found here to do this if you so choose. Also make sure you define the Sheet name you are sourcing the data from:

Sub AddItemsToBox()

    Dim itemcell As Range

    For Each itemcell in Sheets("Source Sheet Name").Range("A1:A5")
        frmAddLineItem.ddlProduct.AddItem itemcell.Value
    Next itemcell

End Sub

Upvotes: 2

Related Questions