Removing the constant link from Spinner

[aaminof]

Hello,

I in Excel, i created a spinner from the Forms menu.

That spinner points to say cell a100, while the spinner itself is in
cell a1.

When i copy the spinner accross to cell b1..q1 the link in the spinner
remains pointing to cell a100.

How can i change that link so that when i copy the spinner accross the
columns, the link will change accordingly.
Thanks.

 

[Dave Peterson]

I find that it's easier to just add lots of objects in code than to fiddle with
the rightclick, format control... stuff.

Maybe you can take this code and test it (and tweak it) in a test workbook.

Option Explicit
Sub testme()

Dim mySpinner As Spinner
Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
.Spinners.Delete 'nice for testing???

Set myRng = .Range("a1:Q1")

For Each myCell In myRng.Cells
With myCell
Set mySpinner = .Parent.Spinners.Add _
(Top:=.Top, _
Left:=.Left + (.Width / 3), _
Width:=.Width / 3, _
Height:=.Height)
End With

With mySpinner
'link it to cell in row 100 (99 rows down)
.LinkedCell = myCell.Offset(99, 0).Address(external:=True)
.Min = 0
.Max = 100
.Value = 0
.Display3DShading = True
.PrintObject = True
End With
Next myCell
End With
End Sub

You may want to fiddle with the positioning (top, left, etc) and the .min, .max
and the rest. But I'd rather experiment a bit than do all those manually.

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