Copying spinners for each cell in Excel 2003?

[RCoasterNY]

I have an inventory tracking sheet that keeps track of my inventory.
I've added in a spinner control to increase/decrease a value in a
certain cell. In my worksheet, the value is located in column E, and
the spinner control is in column F. I have about 200 spinners to add,
and need to fill down the links to it's adjacent cell. I found
something related to checkboxes, but how do I adapt it for the spinner
control? The first value is in E3, and the first spinner is in F3.

 

[Gord Dibben]

Create 200 spiners in F3:F203 and link to E3:E203

Sub add_spinners_and_links()
Dim myRng As Range
Dim Spnr As Spinner
Dim myCell As Range
Dim wks As Worksheet
Set wks = ActiveSheet
With wks
.Spinners.Delete 'testing purposes
Set myRng = .Range("F3:F203")
End With

For Each myCell In myRng.Cells
With myCell
Set Spnr = .Parent.Spinners.Add _
(Top:=.Top, _
Left:=.Left, _
Width:=.Width, _
Height:=.Height)
Spnr.Name = "Spnr_" & .Address(0, 0)
Spnr.LinkedCell = .Offset(0, -1).Address(external:=True)

End With
Next myCell

End Sub


Gord Dibben MS Excel MVP

 

[RCoasterNY]

Create 200 spiners in F3:F203 and link to E3:E203

Sub add_spinners_and_links()
    Dim myRng As Range
    Dim Spnr As Spinner
    Dim myCell As Range
    Dim wks As Worksheet
    Set wks = ActiveSheet
    With wks
        .Spinners.Delete  'testing purposes
        Set myRng = .Range("F3:F203")
    End With

    For Each myCell In myRng.Cells
        With myCell
            Set Spnr = .Parent.Spinners.Add _
                       (Top:=.Top, _
                        Left:=.Left, _
                        Width:=.Width, _
                        Height:=.Height)
            Spnr.Name = "Spnr_" & .Address(0, 0)
            Spnr.LinkedCell = .Offset(0, -1).Address(external:=True)

        End With
    Next myCell

End Sub

Gord Dibben     MS Excel MVP

Many thanks!