Adding combo box across multiple cells

Question

I need to add combo box(ActiveX Control) or Data Validation as drop down list. I have a range of 15 values like, high, low, medium,etc... Have created named range called "priorityvalue".

I can create a dropdown list using combo box by adding named range under ListFillRange in the properties or data validation list by giving named range.

But my concern, I need to dropdown list for 58cells with same values mentioned above. Its tedious job to create combo box for all cells. Please suggest me better option here.

Data validation list serves the purpose. However, it makes user to scroll through dropdown list on each cell unlike combo box it has no input box..

Please suggest

think that this may have already been answered previosly [follow this link](http://stackoverflow.com/questions/17675761/programatically-add-combobox-in-vba-excel) — DaveMac, Sep 09 '16 at 11:38
@DaveMac How do I modify code given in the link you suggested, so that same combo box is added to all the 58 cells available in a column? I am confused. Please help!! — sady, Sep 09 '16 at 11:42
Are the cells in 1 column or are they in multiple columns? i.e. are they going down in the same column or are they going across multiple columns but same row — Zac, Sep 09 '16 at 12:42
@Zac Yes, the cells are under 1 column. I will try the code you given below and will update you. Thankyou — sady, Sep 09 '16 at 15:13
No problem. If it helps, please don't forget to accept the answer — Zac, Sep 11 '16 at 00:39

score 0 · Answer 1 · answered Sep 09 '16 at 13:40

0

Try this:

Sub AddComboBoxToColumns(ByVal oRange As Excel.Range)

    Dim oOLE As OLEObject
    Dim oCell As Object

    ' Loop through all the cells in the range
    For Each oCell In oRange.Cells

        ' Add ComboBox in each cell
        With oCell

            Set oOLE = .Parent.OLEObjects.Add("Forms.combobox.1")
            oOLE.Top = .Top
            oOLE.Left = .Left
            oOLE.Width = .Width
            oOLE.Height = .Height
            oOLE.Name = "ComboBox" & .Address(False, False)
            oOLE.Object.List = Array("Test1", "Test2")

        End With

    Next

    Set oOLE = Nothing

End Sub

NOTE: Call the above function with the range of cells you want to add ComboBox to. You will have to change the Array to use the values you want (you can type them in there or give the range where your existing values are)

answered Sep 09 '16 at 13:40

Zac

1,924
1
8
21

sorry for delay in response. I have concern. You asked me to call the above function from cell. I have created a module by modifying above code as public function and I am trying to call from cell using formula =cell = AddComboBoxToColumns(E1,E57). But I know I am doing something wrong here as Its working. – sady Sep 13 '16 at 13:45
Please help me if the procedure i followed is not what you expected. How do I call with range of cells I want to add combo box – sady Sep 13 '16 at 13:46
@sady, If you want to call it from your worksheet, you can always add a button to your worksheet and call it that way. Otherwise, as you've done, add it to a module and then call it from another function/sub. Let me know if this works – Zac Sep 14 '16 at 07:20
I made two changes in the code. Changed it to public and added a line Set oRange = ActiveSheet.Range("E1:E57"). That worked thank you so so much...!! – sady Sep 14 '16 at 07:32
I have one last question!! How do I reset the contents of combo box when ever I open the workbook? I dont want to see the previous selected value that used when workbook was saved last time. I want a blank cell when ever I open the workbook. – sady Sep 14 '16 at 09:22

score 0 · Accepted Answer · answered Sep 14 '16 at 09:55

Paste the below code in 'ThisWokbook'

Private Sub Workbook_Open()

    Dim oItem As Object

    For Each oItem In Worksheets(1).OLEObjects

        If TypeName(oItem.Object) = "ComboBox" Then

            If Len(oItem.Object.Value) > 0 Then
                oItem.Object.Value = ""
            End If

        End If

    Next

    Set oItem = Nothing

End Sub

NOTE: There are caveats to this. Above code will reset all comboboxes in your worksheet (also, I've set the worksheet to the first worksheet in the workbook, you might want to make that dynamic). If you don't want it to reset all comboboxes and only do the ones you added via the function, you can use the name format to filter the ones you want to clear

Hope this helps

Thank you so much that helped a lot!! – sady Sep 14 '16 at 12:53 — sady, Sep 14 '16 at 12:53

Adding combo box across multiple cells

2 Answers2