ACTIVE X CONTROL MOVES POSITION WHEN MACRO IS RUN

[DeanP]

I am running a macro that unhides rows and inserts an active x check box in my worksheet.
When the macro is run, the check box is placed on row 23, instead of where I want it to
be, E7.


My code is:

With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
    Left:=179, Top:=74, Width:=54.5, Height:=16)
        .Name = "Checkbox1"
        .Object.Caption = "Confirm"
    End With
    
    With ActiveSheet.OLEObjects("CheckBox1").Object
        .Font.Name = "Source Sans Pro"
        .Font.Size = 11
        .SpecialEffect = 0
        .BackStyle = 1
        .BackColor = RGB(47, 117, 181)
        .TextAlign = 2
    End With
Rows("5:21").EntireRow.Hidden = False
End Sub

Any ideas?

[Paul_Hossler]

Seems to work OK here.

I did change the macro a little, but the original still was (basically) near E7

Capture.JPG

Option Explicit
Sub test()
    Dim r As Range
    
    Set r = ActiveSheet.Range("E7")


    With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
        Left:=r.Left, Top:=r.Top, Width:=2 * r.Width, Height:=2 * r.Height)
        .Name = "Checkbox1"
        .Object.Caption = "Confirm"
    End With
    
    With ActiveSheet.OLEObjects("CheckBox1").Object
        .Font.Name = "Source Sans Pro"
        .Font.Size = 11
        .SpecialEffect = 0
        .BackStyle = fmBackStyleOpaque
        .BackColor = RGB(47, 117, 181)
        .TextAlign = fmTextAlignLeft
    End With
    Rows("5:21").EntireRow.Hidden = False
End Sub

[DeanP]

Hi Paul,

Thank you for responding. Even with the changes you suggested, I still have the same problem.

[Logit]

.
As Paul indicated, running your macro places the checkbox pretty much on E7 here as well.

Here is a different macro that will place the checkbox exactly on E7 ... if you want to use it :

Option Explicit


Sub AddBox()
    Dim AddRow As Long
    Dim MyLeft As Double
    Dim MyTop As Double
    Dim MyHeight As Double
    Dim MyWidth As Double
    AddRow = 7
    
            MyLeft = Cells(AddRow, "E").Left
            MyTop = Cells(AddRow, "E").Top
            MyHeight = Cells(AddRow, "E").Height
            MyWidth = MyHeight = Cells(AddRow, "E").Width
            
            ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
            With Selection
                .Caption = ""
                .Value = xlOff
                '.LinkedCell = "A" & AddRow
                .Display3DShading = False
End With
End Sub

[Paul_Hossler]

Hi Paul,

Thank you for responding. Even with the changes you suggested, I still have the same problem.

Can you post a sample workbook that shows the issue? There might be something else going on