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.

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

On Mon, 2 May 2011 08:13:51 -0700 (PDT), RCoasterNY <(E-Mail Removed)>
wrote:

>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.

On May 2, 1:50*pm, Gord Dibben <phnor...@shaw.ca> wrote:
> 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
>
> On Mon, 2 May 2011 08:13:51 -0700 (PDT), RCoasterNY <rcoaste...@gmail.com>
> wrote:
>
> >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.

Many thanks!