CODEWITHSUNDEEP
excelvbacombobox
Noobs DeSroobs
Noobs DeSroobs

Reputation: 283

Creating a list of ComboBoxes using VBA that update the values of cells specified in the code

What I am trying to do is to create a program that adds combo boxes with some options. These options should then, depending on the option selected, change some values in some cells that I specify in code.

This is how I make the combo lists:

Private Sub Workbook_Open()

With Worksheets("Sheet1").Columns("E")
 .ColumnWidth = 25
End With

For i = 1 To 6
    Set curCombo = Sheet1.Shapes.AddFormControl(xlDropDown, Left:=Cells(i, 5).Left, Top:=Cells(i, 5).Top, Width:=100, Height:=15)
    With curCombo
        .ControlFormat.DropDownLines = 3
        .ControlFormat.AddItem "Completed", 1
        .ControlFormat.AddItem "In Progress", 2
        .ControlFormat.AddItem "To be done", 3
        .Name = "myCombo" & CStr(i)
        .OnAction = "myCombo_Change"
    End With
Next i

End Sub

I want each of the dropdown values trigger the event myCombo_Change and then simply change the cell "D" For example, combo box 3 is located at E3 and I want the "To be done" to clear the cell D3 and the completed to simply store the date (and time) to the cell D3. This should be done for all combo boxes in the E Column. 

Private Sub myCombo_Change(index As Integer)

Me.Range("D" & CStr(index)) = Me.myCombo.Value

End Sub

This is the code I started thinking about, but I have no idea how to call the event with an integer as the index parameter NOR how to access the cell using said index.

The effect I want is something along the lines of this:

enter image description here

Upvotes: 1

Views: 413

Answers (1)

user6432984
user6432984

Reputation:

Use Application.Caller to get the name of the control that called the myCombo_Change event.

Sub myCombo_Change()

    Dim curCombo As Shape
    Set curCombo = ActiveSheet.Shapes(Application.Caller)
    curCombo.TopLeftCell.Offset(0, -1) = Now

End Sub

Assign the myCombo_Change to all existing DropDown:

Sub AssignMacroToAllListBoxes()
    Dim sh As Shape
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        For Each sh In ws.Shapes
            If TypeName(sh.OLEFormat.Object) = "DropDown" Then
                sh.OLEFormat.Object.OnAction = "myCombo_Change"
            End If
        Next
    Next
End Sub

Delete all DropDowns on Sheet1

Sub DeleteAllDropDownsOnSheet()
    For Each sh In Sheet1.Shapes
        If TypeName(sh.OLEFormat.Object) = "DropDown" Then
            sh.Delete
        End If
    Next
End Sub

Upvotes: 2

Related Questions