ps. I've found that when I have lots and lots of controls, it's better to use
the vanilla controls from the Forms toolbar.
They seem to have less of an impact on excel--and behave much better than the
controls from the Control toolbox toolbar.
Dave Peterson wrote:
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
--
Dave Peterson