Spinner functionality

Hi All

I have a macro that allows me to enter spinners for a range of cells
(R7:R100)

The problem I have is I would like to format control them all in one
for each incremental cell value :

Current Value: 0
Maximum Value: 450
Incremental Change: 5
Cell Link Q7 through to Q100 respectively e.g. cell link R7 for Q7, R8
for Q8 etc



My Macro:

Sub insertspinners()
Dim mySPN As Spinner
Dim myCell As Range
With ActiveSheet
.Spinners.Delete
For Each myCell In ActiveSheet.Range("R7:R200").Cells
With myCell
Set mySPN = .Parent.Spinners.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)

End With

Next myCell
End With
End Sub

Is this possible?
Thanks!

Just keep going with your code...

Option Explicit

Sub insertspinners()
Dim mySPN As Spinner
Dim myCell As Range
With ActiveSheet
.Spinners.Delete
For Each myCell In ActiveSheet.Range("R7:R200").Cells
With myCell
Set mySPN = .Parent.Spinners.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
End With
With mySPN
.Value = 0
.Max = 450
.SmallChange = 5
.LinkedCell = myCell.Offset(0, 1).Address(external:=True)
End With
Next myCell
End With
End Sub

mellowe wrote:
>
> Hi All
>
> I have a macro that allows me to enter spinners for a range of cells
> (R7:R100)
>
> The problem I have is I would like to format control them all in one
> for each incremental cell value :
>
> Current Value: 0
> Maximum Value: 450
> Incremental Change: 5
> Cell Link Q7 through to Q100 respectively e.g. cell link R7 for Q7, R8
> for Q8 etc
>
> My Macro:
>
> Sub insertspinners()
> Dim mySPN As Spinner
> Dim myCell As Range
> With ActiveSheet
> .Spinners.Delete
> For Each myCell In ActiveSheet.Range("R7:R200").Cells
> With myCell
> Set mySPN = .Parent.Spinners.Add _
> (Top:=.Top, Width:=.Width, _
> Left:=.Left, Height:=.Height)
>
> End With
>
> Next myCell
> End With
> End Sub
>
> Is this possible?
> Thanks!

--

Dave Peterson

Brilliant Thanks Dave - I just wasn't sure how to put in that last
formatting bit, but works a dream...Thanks again!