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.Combobox.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.
Bookmarks