Copying list-box control

I need to copy a list box control into about five hundred
cells. I need the cell link of the control, however, to
change so that it references the cell the control is
pasted into. No matter what I do, however, the cell link
of the pasted control stays the same (not surprisingly)
as in the original control. (And nope, it doesn't seem
to matter whether the cell-link reference is absolute
[including dollar signs] or not.)

If anyone has any ideas, I'd be extremely appreciative--I
really don't want to individually format 300 controls.
Yuck.

What kind of listboxes are they?

Are they from the Forms toolbar or from the Control Toolbox toolbar?

If they're from the control toolbox toolbar, you could have a macro like this
that copies and changes the linked cell.

This might get you started:
Option Explicit
Sub testme()

Dim MstrLB As OLEObject
Dim AnyLB As OLEObject

Dim myRng As Range
Dim myCell As Range

With ActiveSheet
Set myRng = .Range("b1:b10")
Set MstrLB = .OLEObjects("ListBox1")
For Each myCell In myRng.Cells
MstrLB.ShapeRange.Duplicate
Set AnyLB = .OLEObjects(.OLEObjects.Count)
With AnyLB
.Top = myCell.Top
.Left = myCell.Left
.Width = myCell.Width
.Height = myCell.Height
.LinkedCell = myCell.Address(external:=True)
.Name = "Listbox" & myCell.Address(0, 0)
End With
Next myCell
End With

End Sub


If they're listboxes from the Forms toolbar, maybe this'll get you started:

Option Explicit
Sub testme2()

Dim MstrLB As ListBox
Dim AnyLB As ListBox

Dim myRng As Range
Dim myCell As Range

With ActiveSheet
Set myRng = .Range("b1:b10")
Set MstrLB = .ListBoxes("List Box 1")
For Each myCell In myRng.Cells
MstrLB.ShapeRange.Duplicate
Set AnyLB = .ListBoxes(.ListBoxes.Count)
With AnyLB
.Top = myCell.Top
.Left = myCell.Left
.Width = myCell.Width
.Height = myCell.Height
.LinkedCell = myCell.Address(external:=True)
.Name = "Listbox" & myCell.Address(0, 0)
End With
Next myCell
End With

End Sub

===
If you've already copied the listboxes, you could cycle through them and just
change that .linkedcell.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

As an aside, 300 of these listboxes may be too much for excel to
handle--especially the control toolbox versions.

You may want to reconsider your approach--maybe data|validation--or one listbox
that moves where you want it and puts the value in the activecell???


[email protected] wrote:
>
> I need to copy a list box control into about five hundred
> cells. I need the cell link of the control, however, to
> change so that it references the cell the control is
> pasted into. No matter what I do, however, the cell link
> of the pasted control stays the same (not surprisingly)
> as in the original control. (And nope, it doesn't seem
> to matter whether the cell-link reference is absolute
> [including dollar signs] or not.)
>
> If anyone has any ideas, I'd be extremely appreciative--I
> really don't want to individually format 300 controls.
> Yuck.

--

Dave Peterson