Trouble inserting ActiveX control relative to a cell

Question

I am trying to create an excel spreadsheet with a button that inserts a row and an ActiveX combo box above a cell containing a button. The button is selected to size and move with the cell. Since the button moves with each click (as a row above is inserted), and the user could have any cell on the sheet highlighted, I need to create a macro that is relative to the button cell, not the active cell. I accomplished the first half of this by naming the cell containing the button and recording a macro of myself (with Use Relative References selected) inserting a row above the cell containing the button. I then edited the macro containing ActiveCell.Select to Range ("NamedCell").Select so that no matter what cell the user had highlighted, it would insert a row above the button.

The issue I am having is how to insert the ActiveX combo box. The resulting code contains Left, Top, Width, and Height to position the ActiveX combo box. Since the height changes with each successive click and resulting inserted row, all of the combo boxes appear on top of each other. If this cannot happen or requires a ton of VBA code, don't worry about it. I have already learned a bunch by reading through your responses to other people. Here is the macro I currently have, but it inserts the ActiveX combo box in the same place every time instead of in the cell above the button like I want. Thanks again.

Sub AddFitting()
'
' AddFitting Macro
'

'
    Range("FittingRow").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("FittingCell").Select
    ActiveCell.Offset(-1, 0).Range("A1").Select
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=0, Top:=346.5, Width:=120, Height:=15.75) _
        .Select
    With Selection
        .Placement = xlMoveAndSize
        .PrintObject = True
    End With
    ActiveCell.Offset(0, 2).Range("A1").Select
    ActiveCell.FormulaR1C1 = "'= K value"
    ActiveCell.Offset(0, -1).Range("A1").Select
End Sub

score 1 · Answer 1 · answered Dec 13 '13 at 20:21

1

Is this what you are trying?

Sub AddFitting()
    Dim ws As Worksheet
    Dim rng As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        .Range("FittingRow").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

        Set rng = .Range("FittingCell").Offset(-1, 0)

        With .OLEObjects.Add(ClassType:="Forms.ComboBox.1", link:=False, _
        DisplayAsIcon:=False, Left:=rng.Left, Top:=rng.Top, _
        Width:=rng.Width, Height:=rng.Height)
            .Placement = xlMoveAndSize
            .PrintObject = True
        End With

        rng.Offset(, 2).FormulaR1C1 = "'= K value"
    End With
End Sub

answered Dec 13 '13 at 20:21

Siddharth Rout

147,039
17
206
250

Yes, that works perfectly. I was trying to figure out how to position it relative to a cell. Thank you very much for taking the time to help me! Unfortunately I cannot upvote your comment yet because I do not have 15 reputation points, but I will once I do. – Dominic Brown Dec 13 '13 at 20:37
Glad it worked for you. BTW [INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) – Siddharth Rout Dec 13 '13 at 20:38
Thanks, that is very helpful. Sometimes it is tough searching for an answer when you're not exactly sure what you're looking for, but this is definitely helpful. Thanks again. – Dominic Brown Dec 13 '13 at 20:40

Trouble inserting ActiveX control relative to a cell

1 Answers1