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

23419



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