Spinner functionality

[mellowe]

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]

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]

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