spinner copy

[Istanbul]

Hi,

I am trying to copy-paste a spinner control into other cells without
having to relink the new cell. By default, the pasted spinner is still
linked to the original cell which is inconvenient if a large number of
cells are to be changed. Could anyone tell me a short cut?

Many thanks

 

[Dave Peterson]

Have you thought about just adding more spinners via code?

I used the spinner from the Forms toolbar (not from the Controltoolbox toolbar)
and I could do this to plop the spinner in a bunch of cells.

I also used that same cell as the linked cell. But with a numberformat of
";;;", it looks invisible.

Option Explicit
Sub testme02()

Dim mySpinner As Spinner
Dim myRange As Range
Dim myCell As Range

Set myRange = ActiveSheet.Range("a1,b9,c10,c12")

For Each myCell In myRange.Cells
With myCell.Resize(2, 1) '2 rows, 1 column
Set mySpinner = .Parent.Spinners.Add(Top:=.Top, Left:=.Left, _
Height:=.Height, Width:=.Width / 3)
End With

With mySpinner
.Value = 0
.Min = 0
.Max = 100
.SmallChange = 1
.LinkedCell = .TopLeftCell.Address(external:=True)
.Display3DShading = True
End With

myCell.NumberFormat = ";;;"
Next myCell

End Sub

I also resized the spinner to be 2 rows high, by 1/3 of the columnwidth.

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