VBA Code for Creating Spin Button in a Range of Cells

[SorryForTheStupidQuestion]

Hi All,

I am trying to teach myself VBA. I have created some examples that I am trying to solve but I have no actual need to complete these tasks (other than to learn the code itself). With that said, what I am trying to do in this example is create a macro that inserts a spin button in each cell in range (e2:e7) in this spreadsheet:



I was able to successfully do this above by first creating the spin button in cell E2 and then using this VBA code which I learn from a video to copy/paste additional spin buttons:

Sub SpinBoxGolf()

Dim Cyclecount As Long


For Cyclecount = 1 To 5


    ActiveSheet.Shapes("Spinner 1").Copy
    ActiveSheet.Paste


    Selection.Top = Range("E" & Cyclecount + 2).Top
    Selection.Left = Range("E" & Cyclecount + 2).Left
    
    With Selection
    .Value = 70
    .Min = 50
    .Max = 100
    .SmallChange = 1
    .LinkedCell = "D" & Cyclecount + 2
    End With
    
Next Cyclecount


End Sub

However, what I would like to do is to be able to learn how to write code so that rather than having to first create a spin button and then copy paste it using a macro I can instead run a macro that will create all spin buttons (including the first one). I recorded a macro where I create a spin button but I wasn't able to make any sense of the code.

I would also like to know if it is possible to make this part of the code dynamic;

With Selection
    .Value = 70

Ideally this value would populate as the value in corresponding cell in C column but it didn't seem to like this when I tried it:

With Selection
    .Value = activesheet.range("c" & Cyclecount + 2).value

Any help would be greatly appreciated.

 

[James006]

Hello,

If you need to dig into how to create multiple Spin Buttons ...

Below is a nice tutorial (in 4 lessons) from Chris Mortimer ...

https://www.youtube.com/watch?v=Pmya_vSIFvU

Hope this will help

 

[Yongle]

try this

Sub Spinning()
    Dim spinner As OLEObject, cel As Range
    For Each cel In ActiveSheet.Range("E2:E7")
'add spinner
        Set spinner = cel.Parent.OLEObjects.Add(ClassType:="Forms.SpinButton.1", Top:=cel.Top, Left:=cel.Left, Height:=cel.RowHeight, Width:=15)
'set spinner properties
        With spinner
            .LinkedCell = cel.Offset(, -1).Address(0, 0)
            With .Object
                .SmallChange = 1
                .BackColor = &H80FF80
                .Value = cel.Offset(, -2)
                'etc
            End With
        End With
    Next cel
End Sub

 

[SorryForTheStupidQuestion]

Hello,

If you need to dig into how to create multiple Spin Buttons ...

Below is a nice tutorial (in 4 lessons) from Chris Mortimer ...

https://www.youtube.com/watch?v=Pmya_vSIFvU

Hope this will help

Thanks for the reply. This was actually the video I used to get as far as I have, but in that instance he copies and pastes the first button rather than using code to create it from scratch. Still a very functional and practical way to do it but was hoping to learn the code to create as well.

 

[SorryForTheStupidQuestion]

try this

Sub Spinning()
    Dim spinner As OLEObject, cel As Range
    For Each cel In ActiveSheet.Range("E2:E7")
'add spinner
        Set spinner = cel.Parent.OLEObjects.Add(ClassType:="Forms.SpinButton.1", Top:=cel.Top, Left:=cel.Left, Height:=cel.RowHeight, Width:=15)
'set spinner properties
        With spinner
            .LinkedCell = cel.Offset(, -1).Address(0, 0)
            With .Object
                .SmallChange = 1
                .BackColor = &H80FF80
                .Value = cel.Offset(, -2)
                'etc
            End With
        End With
    Next cel
End Sub

Hi Yongle and thanks for the reply. When I try this I get a 1004 run time error - Cannot start the source application for this object. I've not yet come across the Parent function or OLEObjects but I am using a Mac - not sure if that might be an issue. I will endeavour to learn more about those functions and try to make it work.

 

[Yongle]

I will leave it with you until you post again - it works with windows and I cannot test with a Mac
If you cannot get it to work, I will post an alternative version