CODEWITHSUNDEEP
vbaexcelcombobox
Shan S
Shan S

Reputation: 49

Move Combobox onclick of a button to a next row excel VBA

I have created a vba code to create combobox and populated with my named range. Now i want to make when i select something from a combobox it has to set that value in that current cell and it should move to next row so that i can keep setting a value in each cell from a combobox.

i have this following code to create combobox , but i dont know how to make it move to a next row with .onaction 

Sub AddComboBoxes()
Dim cb As Object
Dim aCell As Range, r As Long


For i = 1 To 1
    Set aCell = Sheet1.Cells(i, 5)
    Set cb = Sheet1.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Left:=aCell.Left, Top:=aCell.Top, Width:=aCell.Width, Height:=aCell.Height)
    cb.Placement = xlMoveAndSize
    cb.Name = "ComboBoxN1"
    cb.ListFillRange = "N1"
    cb.OnAction = "N1.value"

Next

End Sub

Please help.

Upvotes: 0

Views: 2914

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

Like I mentioned .OnAction is not a property of OLEObjects but of DropDowns

See this example which will create the dropdowns 5 times in Col 5 and assign "Sheet2!A1:A5" as ListFillRange and execute the CallMe when you select an option in the DropDown.

LOGIC:

  1. Name your DropDowns like "ComboBoxN" & i in a loop so that we can retrieve the row they are on later.
  2. Your post says that the value of the combo should be set in the current cell. I am using Col A cells in my example below. Change as applicable.
  3. I am assuming that your DropDowns start from row one. If not then you will have to change the logic of ascertaining the row number in Sub CallMe()

Code:

Sub AddComboBoxes()
    Dim cb As Object
    Dim aCell As Range
    Dim i As Long

    For i = 1 To 5
        Set aCell = Sheet1.Cells(i, 5)
        Set cb = Sheet1.DropDowns.Add(aCell.Left, aCell.Top, aCell.Width, aCell.Height)
        cb.Placement = xlMoveAndSize
        cb.Name = "ComboBoxN" & i
        cb.ListFillRange = "Sheet2!A1:A5"
        cb.OnAction = "CallMe"
    Next
End Sub

Your CallMe should be something like this

Sub CallMe()
    Dim rw As Long
    Dim cb As Object

    '~~> Extract the number from the dropdown to
    '~~> identify which row is it in
    rw = Val(Trim(Replace(Application.Caller, "ComboBoxN", "")))

    Set cb = Sheet1.DropDowns(rw)

    '~~> We are setting the value in Col A. Chnage as applicable
    Sheet1.Range("A" & rw).Value = cb.Value

    '~~> Activate the next cell.
    Sheet1.Range("A" & rw + 1).Activate
End Sub

Screenshot:

enter image description here

Upvotes: 3

Related Questions