ActiveX control top property is different from what was specified
I'm trying to add a bunch of the ActiveX controls to an excel spreadsheet.
I feed single cell range names sNameOfRange (string) one at a time to the attached code which is below .
For each Range it creates an ActiveX combo box (not the one from the forms collection, but the other one)
I would like each control to occupy the full row height and be nice and centred
IE. Be virtually the same size as the cell that it sits on And be nice and centred on that cell
My issue is the top of the control is not ever where it's supposed to be.
I have tried adding an adjustment but for some reason the amount of adjustment required for each row is variable even though the row heights are all the same.
I bet it's really simple problem but I can't see where it lies.
I have observed that the more repetitions this code performs the more the error becomes, it's something cumulative.
I feel that somehow when the new combo boxes are created they are using a parameter from a combo which has been created & destroyed before?
PS I can manually move the control to the correct place so I know it's possible for it to be in the right place.
Why aren't the controls placing where I want them and How to fix it?
Any help would be greatly appreciated !
Thank you in advance
A screenshot follows the debug printout, both tell the story
-Please scroll down to the bottom to fiew the code.
Top Desired =430.5
Top Called for =439.25 (value fed to .OLEObjects.Add Top:=), actually is =432
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =448.5
Top Called for =457.25 (value fed to .OLEObjects.Add Top:=), actually is =449.25
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =466.5
Top Called for =475.25 (value fed to .OLEObjects.Add Top:=), actually is =467.25
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =484.5
Top Called for =493.25 (value fed to .OLEObjects.Add Top:=), actually is =484.5
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =502.5
Top Called for =511.25 (value fed to .OLEObjects.Add Top:=), actually is =502.5
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =520.5
Top Called for =529.25 (value fed to .OLEObjects.Add Top:=), actually is =520.5
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =556.5
Top Called for =565.25 (value fed to .OLEObjects.Add Top:=), actually is =555.75
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =574.5
Top Called for =583.25 (value fed to .OLEObjects.Add Top:=), actually is =573
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =592.5
Top Called for =601.25 (value fed to .OLEObjects.Add Top:=), actually is =591
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =610.5
Top Called for =619.25 (value fed to .OLEObjects.Add Top:=), actually is =609
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =628.5
Top Called for =637.25 (value fed to .OLEObjects.Add Top:=), actually is =626.25
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =646.5
Top Called for =655.25 (value fed to .OLEObjects.Add Top:=), actually is =644.25
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =664.5
Top Called for =673.25 (value fed to .OLEObjects.Add Top:=), actually is =661.5
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =682.5
Top Called for =691.25 (value fed to .OLEObjects.Add Top:=), actually is =679.5
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =700.5
Top Called for =709.25 (value fed to .OLEObjects.Add Top:=), actually is =697.5
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =718.5
Top Called for =727.25 (value fed to .OLEObjects.Add Top:=), actually is =714.75
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =754.5
Top Called for =763.25 (value fed to .OLEObjects.Add Top:=), actually is =750
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =772.5
Top Called for =781.25 (value fed to .OLEObjects.Add Top:=), actually is =768
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =790.5
Top Called for =799.25 (value fed to .OLEObjects.Add Top:=), actually is =786
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =808.5
Top Called for =817.25 (value fed to .OLEObjects.Add Top:=), actually is =803.25
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =826.5
Top Called for =835.25 (value fed to .OLEObjects.Add Top:=), actually is =821.25
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =844.5
Top Called for =853.25 (value fed to .OLEObjects.Add Top:=), actually is =838.5
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =862.5
Top Called for =871.25 (value fed to .OLEObjects.Add Top:=), actually is =856.5
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =880.5
Top Called for =889.25 (value fed to .OLEObjects.Add Top:=), actually is =874.5
Height should be =18 actually is =16.5
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =898.5
Top Called for =907.25 (value fed to .OLEObjects.Add Top:=), actually is =891
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =916.5
Top Called for =925.25 (value fed to .OLEObjects.Add Top:=), actually is =909
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =934.5
Top Called for =943.25 (value fed to .OLEObjects.Add Top:=), actually is =926.25
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =952.5
Top Called for =961.25 (value fed to .OLEObjects.Add Top:=), actually is =944.25
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =970.5
Top Called for =979.25 (value fed to .OLEObjects.Add Top:=), actually is =962.25
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =1006.5
Top Called for =1015.25 (value fed to .OLEObjects.Add Top:=), actually is =997.5
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =1024.5
Top Called for =1033.25 (value fed to .OLEObjects.Add Top:=), actually is =1014.75
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =1042.5
Top Called for =1051.25 (value fed to .OLEObjects.Add Top:=), actually is =1032.75
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =1060.5
Top Called for =1069.25 (value fed to .OLEObjects.Add Top:=), actually is =1050.75
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Controlsnotplacingright.jpg
I feed single cell range names sNameOfRange (string) one at a time to the attached code which is below .
For each Range it creates an ActiveX combo box (not the one from the forms collection, but the other one)
I would like each control to occupy the full row height and be nice and centred
IE. Be virtually the same size as the cell that it sits on And be nice and centred on that cell
My issue is the top of the control is not ever where it's supposed to be.
I have tried adding an adjustment but for some reason the amount of adjustment required for each row is variable even though the row heights are all the same.
I bet it's really simple problem but I can't see where it lies.
I have observed that the more repetitions this code performs the more the error becomes, it's something cumulative.
I feel that somehow when the new combo boxes are created they are using a parameter from a combo which has been created & destroyed before?
PS I can manually move the control to the correct place so I know it's possible for it to be in the right place.
Why aren't the controls placing where I want them and How to fix it?
Any help would be greatly appreciated !
Thank you in advance
A screenshot follows the debug printout, both tell the story
-Please scroll down to the bottom to fiew the code.
Top Desired =430.5
Top Called for =439.25 (value fed to .OLEObjects.Add Top:=), actually is =432
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =448.5
Top Called for =457.25 (value fed to .OLEObjects.Add Top:=), actually is =449.25
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =466.5
Top Called for =475.25 (value fed to .OLEObjects.Add Top:=), actually is =467.25
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =484.5
Top Called for =493.25 (value fed to .OLEObjects.Add Top:=), actually is =484.5
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =502.5
Top Called for =511.25 (value fed to .OLEObjects.Add Top:=), actually is =502.5
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =520.5
Top Called for =529.25 (value fed to .OLEObjects.Add Top:=), actually is =520.5
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =556.5
Top Called for =565.25 (value fed to .OLEObjects.Add Top:=), actually is =555.75
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =574.5
Top Called for =583.25 (value fed to .OLEObjects.Add Top:=), actually is =573
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =592.5
Top Called for =601.25 (value fed to .OLEObjects.Add Top:=), actually is =591
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =610.5
Top Called for =619.25 (value fed to .OLEObjects.Add Top:=), actually is =609
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =628.5
Top Called for =637.25 (value fed to .OLEObjects.Add Top:=), actually is =626.25
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =646.5
Top Called for =655.25 (value fed to .OLEObjects.Add Top:=), actually is =644.25
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =664.5
Top Called for =673.25 (value fed to .OLEObjects.Add Top:=), actually is =661.5
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =682.5
Top Called for =691.25 (value fed to .OLEObjects.Add Top:=), actually is =679.5
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =700.5
Top Called for =709.25 (value fed to .OLEObjects.Add Top:=), actually is =697.5
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =718.5
Top Called for =727.25 (value fed to .OLEObjects.Add Top:=), actually is =714.75
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =754.5
Top Called for =763.25 (value fed to .OLEObjects.Add Top:=), actually is =750
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =772.5
Top Called for =781.25 (value fed to .OLEObjects.Add Top:=), actually is =768
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =790.5
Top Called for =799.25 (value fed to .OLEObjects.Add Top:=), actually is =786
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =808.5
Top Called for =817.25 (value fed to .OLEObjects.Add Top:=), actually is =803.25
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =826.5
Top Called for =835.25 (value fed to .OLEObjects.Add Top:=), actually is =821.25
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =844.5
Top Called for =853.25 (value fed to .OLEObjects.Add Top:=), actually is =838.5
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =862.5
Top Called for =871.25 (value fed to .OLEObjects.Add Top:=), actually is =856.5
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =880.5
Top Called for =889.25 (value fed to .OLEObjects.Add Top:=), actually is =874.5
Height should be =18 actually is =16.5
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =898.5
Top Called for =907.25 (value fed to .OLEObjects.Add Top:=), actually is =891
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =916.5
Top Called for =925.25 (value fed to .OLEObjects.Add Top:=), actually is =909
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =934.5
Top Called for =943.25 (value fed to .OLEObjects.Add Top:=), actually is =926.25
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =952.5
Top Called for =961.25 (value fed to .OLEObjects.Add Top:=), actually is =944.25
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =970.5
Top Called for =979.25 (value fed to .OLEObjects.Add Top:=), actually is =962.25
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =1006.5
Top Called for =1015.25 (value fed to .OLEObjects.Add Top:=), actually is =997.5
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =1024.5
Top Called for =1033.25 (value fed to .OLEObjects.Add Top:=), actually is =1014.75
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =1042.5
Top Called for =1051.25 (value fed to .OLEObjects.Add Top:=), actually is =1032.75
Height should be =18 actually is =18
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Top Desired =1060.5
Top Called for =1069.25 (value fed to .OLEObjects.Add Top:=), actually is =1050.75
Height should be =18 actually is =17.25
Left should be =0 actually is =0
Width should be =117.75 actually is =117.75
Controlsnotplacingright.jpg
AddControl:
Dim sNameC1 As String
Dim sNameLinkedCell As String
Dim sNameListFillRange As String
Dim sSheetDesignator As String
Dim sNameBody As String
Dim sIndex As String
Const cFillRange = "Table"
Const cLinkedCell = "Key"
Const cAdjTop = 8.75
Const cAdjLeft = 0
Const cAdjWidth = -0.25
Const cAdjHeight = 0
ctrlTop = Range(sNameOfRange).Offset(0, -1).Top + cAdjTop
ctrlLeft = Range(sNameOfRange).Offset(0, -1).Left '+ cAdjLeft
ctrlWidth = Range(sNameOfRange).Offset(0, -1).Width '+ cAdjWidth
ctrlHeight = Range(sNameOfRange).Offset(0, -1).Height '+ cAdjHeight
With ActiveSheet
With .OLEObjects.Add(ClassType:="Forms." & cCtrlType1 & ".1", link:=False, _
DisplayAsIcon:=False, Left:=ctrlLeft, Top:=ctrlTop, Width:=ctrlWidth, _
Height:=ctrlHeight)
Debug.Print "Top Desired =" & Range(sNameOfRange).Offset(0, -1).Top
Debug.Print "Top Called for =" & ctrlTop & " (value fed to .OLEObjects.Add Top:=), actually is =" & .Top
Debug.Print "Height should be =" & ctrlHeight & " actually is =" & .Height
Debug.Print "Left should be =" & ctrlLeft & " actually is =" & .Left
Debug.Print "Width should be =" & ctrlWidth & " actually is =" & .Width & vbCr
I don't know if this will help you (and trying to keep controls on a sheet is strange, at best).
Right click on the control and select properties.
Depending on Excel version - look around for MOVE AND SIZE WITH CELLS, MOVE BUT DON'T SIZE WITH CELLS, and DON'T MOVE OR SIZE WITH CELLS. Select the best one for your needs.
Scott C
Right click on the control and select properties.
Depending on Excel version - look around for MOVE AND SIZE WITH CELLS, MOVE BUT DON'T SIZE WITH CELLS, and DON'T MOVE OR SIZE WITH CELLS. Select the best one for your needs.
Scott C
further... Objects like text boxes, combo boxes, etc. don't act the same on sheets as they do on forms. Forms are pretty well 'static' and the TOP is the TOP from the top of form. Since sheets can word wrap, expand any singe row, etc. the TOP isn't that straight forward on sheets as forms.
Scott c
Scott c
No changes are made to the sheet
None of the dimensions change, especially not in the micro second between then the combo is created and the top property is trapped in the debug showing its not what was called for.
Please See the picture above
Its related to the sheet
If I create a new worksheet with a named range "TestRange" it works perfectly.
I have added
.Select
Selection.ShapeRange.Incre
as a work around and it works fine.
If the row is 18 high then the height is 9.75 out, this varies but not with row height
For some reason the control is not accepting the height parameter correctly when it is created; I also notice its picking up its format from the format of the cell above
The exact centre of the control is over the cell above.
Perhaps there is some corruption in that sheet
I will accept my workaround as the solution & move on unless anyone can point me at the actual cause why top is not taking so I can fix and workaround becomes unnecessary.
None of the dimensions change, especially not in the micro second between then the combo is created and the top property is trapped in the debug showing its not what was called for.
Please See the picture above
Its related to the sheet
If I create a new worksheet with a named range "TestRange" it works perfectly.
I have added
.Select
Selection.ShapeRange.Incre
as a work around and it works fine.
If the row is 18 high then the height is 9.75 out, this varies but not with row height
For some reason the control is not accepting the height parameter correctly when it is created; I also notice its picking up its format from the format of the cell above
The exact centre of the control is over the cell above.
Perhaps there is some corruption in that sheet
I will accept my workaround as the solution & move on unless anyone can point me at the actual cause why top is not taking so I can fix and workaround becomes unnecessary.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial