Formatting programatically added controls to fit in cells

user_5273867 0
Viewed 44

I'm creating a program in Visual Basic through Microsoft Excel, where I can input all the information from an application into excel. Each row is unique and features a dropdown control that is created and placed programatically (see code below).

The problem I have come across is correctly placing the control in each cell within one of the columns.

I have read through documents on the web and seen examples on other questions but none of them seem to work for my situation. I keep having to guess-n-check the x and y values for the AddFormControl function to place the controls in a somewhat correct way.

Code:

For y = 1 To UBound(array) + 1

   Set lb = Sheet1.Shapes.AddFormControl(xlDropDown, 274, 32 + (19 * y), 77, 19)
   With lb
       .ControlFormat.DropDownLines = 3
       .ControlFormat.AddItem "Select a value", 1
       .ControlFormat.AddItem "Auto", 2
       .ControlFormat.AddItem "Default", 3
       .ControlFormat.Value = 1
   End With

Next y

I have counted the width's and height's of all the rows and columns so that I can place the controls in the correct spot but they are offset, some more than others.

The 274 x-value above is what lines up to the spot I would like it to for the x-axis on the excel sheet but is not how wide the columns prior to the spot add up to. Same goes for the y-value 77.

What would be the most efficient solution to this issue?

vba excel
1 Answers

You can actually use Range.Top and Range.Left to place your From Controls exactly where you want them and they will be perfectly lined up with the cell in question. This is a great method for adding them in large quantities dynamically.

user_92061710
Related