OLEObjects.Add strange behaviour
 

I dont know if this the right place to submit what I suspect to be a bug
in the OLEObjects Add method. Please apologize if not.

When invoking the Add method on the OLEObjects collection of a worksheet
we can indicate the coordinates and size of the control. This method
seems not to work correctly when the zoom is set to a value lower than
100%. The position of the control is good except for it's Top value.
If the coordinates are set after the control's creation, the position is
correct.
Here is some example code :

Sub IsThisABug()
Dim aRange As range

Application.ActiveWindow.Zoom = 50

Set aRange = range("A1500")
aRange.Select
aRange.Show

' The control appears around cell A1416 instead of A1500
Application.ActiveSheet.OLEObjects.Add "Forms.Combobox.1" _
, , , , , , , _
aRange.Left, aRange.Top, aRange.Width, aRange.Height

Set aRange = Nothing
End Sub

Sub ThisOneWorks()
Dim aRange As range

Application.ActiveWindow.Zoom = 50

Set aRange = range("A1500")
aRange.Select
aRange.Show

' The control appears exactly on cell A1500
With Application.ActiveSheet.OLEObjects.Add("Forms.Comb obox.1")
.Left = aRange.Left
.Top = aRange.Top
.Width = aRange.Width
.Height = aRange.Height
End With

Set aRange = Nothing
End Sub

I'm not an Excel or VBA expert, so I hope not to have missed something.

PS: to contact me, replace the domain name of my address by prosim.net.

OLEObjects.Add strange behaviour
 

I forgot to mention that the "bug" can be reproduced on all versions of
Excel I can test (97, 2000, XP, 2003).

Pierre Castelain a écrit :
I dont know if this the right place to submit what I suspect to be a bug
in the OLEObjects Add method. Please apologize if not.

When invoking the Add method on the OLEObjects collection of a worksheet
we can indicate the coordinates and size of the control. This method
seems not to work correctly when the zoom is set to a value lower than
100%. The position of the control is good except for it's Top value.
If the coordinates are set after the control's creation, the position is
correct.
Here is some example code :

Sub IsThisABug()
Dim aRange As range

Application.ActiveWindow.Zoom = 50

Set aRange = range("A1500")
aRange.Select
aRange.Show

' The control appears around cell A1416 instead of A1500
Application.ActiveSheet.OLEObjects.Add "Forms.Combobox.1" _
, , , , , , , _
aRange.Left, aRange.Top, aRange.Width, aRange.Height

Set aRange = Nothing
End Sub

Sub ThisOneWorks()
Dim aRange As range

Application.ActiveWindow.Zoom = 50

Set aRange = range("A1500")
aRange.Select
aRange.Show

' The control appears exactly on cell A1500
With Application.ActiveSheet.OLEObjects.Add("Forms.Comb obox.1")
.Left = aRange.Left
.Top = aRange.Top
.Width = aRange.Width
.Height = aRange.Height
End With

Set aRange = Nothing
End Sub

I'm not an Excel or VBA expert, so I hope not to have missed something.

PS: to contact me, replace the domain name of my address by prosim.net.