We help IT Professionals succeed at work.

Get Started
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

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
```

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy

Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif

Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster

CTP, Sr Infrastructure Consultant

Connect with Certified Experts to gain insight and support on specific technology challenges including:

- Troubleshooting
- Research
- Professional Opinions

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE