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?

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?

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?

I don't know how to change the linked cell for each spinner currently on a
sheet.

Here is code to create 25 spinners down column B with incrementing linked
cell reference. Maybe that can help?

Sub add_spinner()
With ActiveSheet
For i = 1 To 25
Set cb = .Shapes.AddFormControl(xlSpinner, 70, i * 20, 15, 15)
cb.ControlFormat.LinkedCell = "A" & i
Next
End With
End Sub


Gord

On Wed, 26 Aug 2009 13:50:05 -0700, 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?

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

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

Dave

I thought of this also but OP has over 100 spinners and just wants to change
25 of them.

Any way to single out those 25 spinners from a group of 100 on the sheet?

Other than by name, that is?

If OP has simply copied them as he states, the names may not be in any
particular sequence.


Gord

On Wed, 26 Aug 2009 18:22:32 -0500, 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?

I should have read the thread more closely.

If every spinner that should be modified is in row 1-25 (and all those in rows
1-25 should be touched), then something like:

For Each mySpinner In wks.Spinners
With mySpinner
if .topleftcell.row 25 then
'do nothing
else
.LinkedCell = wks.Cells(.TopLeftCell.Row, "A") _
.Address(external:=True)
end if
End With
Next mySpinner

Same kind of thing if the names were nice--Spinner_### (say).

I think it's time to wait to see if the OP can help define how to classify his
spinners (and which toolbar they came from).

Gord Dibben wrote:

Dave

I thought of this also but OP has over 100 spinners and just wants to change
25 of them.

Any way to single out those 25 spinners from a group of 100 on the sheet?

Other than by name, that is?

If OP has simply copied them as he states, the names may not be in any
particular sequence.

Gord

On Wed, 26 Aug 2009 18:22:32 -0500, 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