0

I need to dynamically create and delete 1 - N number of listboxes on a worksheet. Because the page will be changing I can't know in advance where to place the control so a statement like ... Left:=10, Top:=10, Width:=106.8, Height:=154.6 won't work. I need to be able to programmatically place controls at C1 or C55 for example.

The research I've done found only examples of static creation at a fixed location and I also haven't been able to find anything that will give me the coordinates of a cell (ex: C1 = Left:=65, Top:87).

Thanks for your help.

willlow1044
  • 111
  • 2
  • 12

1 Answers1

1

Consider this:

Dim i&, n&, r As Range

n = 3
With ActiveSheet
    For i = 1 To n
        Set r = .Range("c1")(i)
        .ListBoxes.Add r.Left, r.Top, r.Width, r.Height
    Next
End With

This simply demonstrates how to use the dimensions and coordinates of a cell as the dimensions and coordinates of the control. It will be off a bit on height. You can adjust that.

Additionally you can use whatever mechanism you wish to identify the cells. I just did the three top cells of column C.

Excel Hero
  • 14,253
  • 4
  • 33
  • 40
  • Good answer. Adds a `FormControl` listbox. Alternatively to add a `ActiveX` listbox use `.OLEObjects.Add(ClassType:="Forms.ListBox.1", ...` – chris neilsen Nov 09 '15 at 22:24