Are these spinners from the Forms toolbar?

If yes (and you've located the spinners within the row like you said you did!):

Option Explicit
Sub testme()

Dim mySpinner As Spinner
Dim wks As Worksheet

Set wks = ActiveSheet

For Each mySpinner In wks.Spinners
With mySpinner
.LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _
.Address(external:=True)
End With
Next mySpinner

End Sub


If they're spin buttons from the Control toolbox toolbar:

Option Explicit
Sub testme()

Dim OLEObj As OLEObject
Dim wks As Worksheet

Set wks = ActiveSheet

For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.SpinButton Then
With OLEObj
.LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _
.Address(external:=True)
End With
End If
Next OLEObj

End Sub



Adam Ronalds wrote:

Ok, but I wanted each spinner to have a different cell reference??? In other
words if I have a spinner on each row for 25 rows in Cells B1:B25 and I want
each spinner to reference A1, A2, A3, etc., how do I copy the spinner in cell
B1 into cells B2:B25 and have the cell reference change for the copied
spinners so that I don't have to go into each and change the individual
references?

"Gord Dibben" wrote:

F5SpecialObjectsOK

With all spinners selected right-click on one of them and change the linked
cell.

Will be done to all.


Gord Dibben MS Excel MVP

On Wed, 26 Aug 2009 12:07:02 -0700, Adam Ronalds <Adam
wrote:

I am copying a spin button several times (over 100 spin buttons in this input
spreadsheet) and rather than having to go into the "format control" screen
100+ times, I was hoping that there was a way to quickly and easily change
the cell link for each of the copies spin buttons. I removed the anchors in
the "cell link" filed of the format control however, when I copy and paste
the sin button, this cell link character doesn't change. Is there an quicker
way to change the "cell link" for each copied spin button?



--

Dave Peterson