excel - 以编程方式将 ComboBox 插入单元格内

Question

我想在事件单元格内插入对象 curCombo 组合框，而不定义宽度和高度。在我的一些测试中，我遵循了这种原则，但我也没有做到这一点

Set curCombo = ActiveSheet.Shapes.AddFormControl(.Left, .Top, .Width, .Height)

这是我的代码:

Sub comboBox1()
    Dim curCombo As Object

    '// Main challange is this set procedure here.
    Set curCombo = ActiveSheet.Shapes.AddFormControl(xlDropDown, _
    Left:=Cells(ActiveCell.Row, 3).Left, _
    Top:=Cells(ActiveCell.Row, 3).Top, Width:=100, Height:=20)

    With curCombo
        .ControlFormat.DropDownLines = 3
        .ControlFormat.AddItem "1", 1
        .ControlFormat.AddItem "2", 2
        .ControlFormat.AddItem "3", 3
        .Name = "myCombo" & ER.Row
        '.OnAction = "myCombo_Change" & ER.row
    End With
End Sub

Answer 1

do you mean which ever cell you specify, it is should overlap that? – Siddharth Rout 1 hour ago

Yes Sir. Thant is the case. – AratioD 3 mins ago

这就是你正在尝试的吗？

Sub comboBox1()
    Dim curCombo As Object
    Dim ws As Worksheet
    Dim rng As Range

    '~~> Change this to the relevant sheet
    Set ws = ActiveSheet

    With ws
        '~~> Change this to the relevant cell where
        '~~> you want the combobox
        Set rng = .Range("B5")

        Set curCombo = .Shapes.AddFormControl(xlDropDown, _
                                              Left:=rng.Left, _
                                              Top:=rng.Top, _
                                              Width:=rng.Width, _
                                              Height:=rng.Height)

        With curCombo
            .ControlFormat.DropDownLines = 3
            .ControlFormat.AddItem "1", 1
            .ControlFormat.AddItem "2", 2
            .ControlFormat.AddItem "3", 3
            .Name = "myCombo" & ER.Row
            '.OnAction = "myCombo_Change" & ER.row
        End With
    End With
End Sub

屏幕截图