VBA to change spin value?

[612Guy]

Is it possible to change a spin value using VBA? I tried using the macro recorder but it does not record any code when I manipulate the control.
I would like to have VBA print the current sheet to PDF, name the file after the value in Letter!$O$1, and then loop through (press the up arrow/spin the control in cell Letter!$M$1) until at the max spin value. I suppose another way to handle it would be to have VBA change the cell d!$A$2 which houses all the data (screenshot 2). The spinner control in Letter!$M$1 is linked to the value in cell d!$A$2.

Appreciate any guidance.

 

[Micron]

WRT the spinner, not sure what you're asking for. If you want a value in the cell why not just type it in and forget the spinner? If I want the cell to be a number, surely I have that number in mind and could just type it in? Or maybe you're asking about adjusting the min/max or step values, but I still think the spinner is a somewhat useless control most of the time. If I understood the vba comment, it still doesn't make sense to me to write code that will put 44 in a cell when I can just key it in.

Can't help much with the other parts - would have to google it and find some code examples so others will probably step in and provide that while I spend lotsa time finding out how. Happens to me all the time.

 

[Domenic]

Here are two methods that hopefully do what you want. The first method loops through each spinner value, as per your request. The other loops through each record in your table. With either method though, you may need to pause/DoEvents, so I have included it in the solution. Note that I have assumed that the Spinner is taken from Form Controls, not ActiveX Controls. And I have also assumed the data in the sheet named "d" has been converted into a Table. So first add the following procedure to your module...

Sub PauseMacro(ByVal secs As Long)

    Dim endTime As Single
    endTime = Timer + secs
 
    Do
        DoEvents
    Loop Until Timer > endTime
 
End Sub

Then try the following...

Looping through each spinner value

    Dim theSpinner As Spinner
    Set theSpinner = ThisWorkbook.Worksheets("Letter").Spinners("Spinner 1") 'change the name of the spinner accordingly
 
    Dim i As Long
    With theSpinner
        For i = .Min To .Max
            .Value = i
            'your code to print to PDF, etc
            '
            '
            PauseMacro 3 'seconds
        Next i
    End With

Looping through each table record

    Dim records As Variant
    records = ThisWorkbook.Worksheets("d").ListObjects("Table1").ListColumns("Record").DataBodyRange.Value 'change the name of the table accordingly
 
    Dim arrIndex As Long
    For arrIndex = LBound(records) To UBound(records)
        ThisWorkbook.Worksheets("d").Range("A2").Value = records(arrIndex, 1)
        'your code to print to PDF, etc
        '
        '
        PauseMacro 3 'seconds
    Next arrIndex

Hope this helps!