Limit an Excel Spin Button and Print All Student Reports with VBA
Question details
Limit a Form Control spin button to the student list and print a report for every student using VBA.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A class report workbook uses a spin button to select among 52 students and an existing macro to print each report.
- Observed behavior
- The spin button should stop at the final student, and a print-all macro should select and print each student's report.
Confirm that the 52 student names are in SS1 GOLD!B5:B56, identify the worksheet containing selector cell I232, and check that Macro9 prints the currently selected student's report. Use a macro-enabled workbook and enable macros only if you trust the file.
Set the spin button limits and add a print-all VBA macro
Use this approach when the spin button starts at zero and Macro9 already prints the selected report.
Right-click the Form Control spin button and select Format Control. On the Control tab, set Minimum value to 0 and Maximum value to 51, then click OK. This provides 52 positions, numbered 0 through 51.
Open the VBA editor with Alt+F11, select Insert > Module, and add the code below. Replace "Report" with the actual name of the worksheet containing I232: Sub PrintAllSS1Gold() Dim wsList As Worksheet Dim wsReport As Worksheet Dim i As Long Set wsList = ThisWorkbook.Worksheets("SS1 GOLD") Set wsReport = ThisWorkbook.Worksheets("Report") For i = 0 To 51 wsReport.Range("I232").Value = wsList.Range("B" & (5 + i)).Value Call Macro9 Next i End Sub
Return to Excel, right-click the PRINT ALL IN SS1 GOLD button, choose Assign Macro, select PrintAllSS1Gold, and click OK.
Use the spin button to verify it stops at the first and last students. Check that Macro9 prints the report for the name in I232, then click PRINT ALL IN SS1 GOLD to run the loop.

Manage student report spreadsheets with WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Microsoft Office formats, making it easy to move everyday report workbooks. Excel-specific Form Controls and VBA macros may require separate compatibility testing.

Frequently Asked Questions
Why is the maximum set to 51 for 52 students?
The spin button starts at 0, so the positions 0 through 51 represent 52 students. Set both values under Format Control > Control.
Which student rows does the print-all macro use?
The loop reads SS1 GOLD!B5:B56: it starts at row 5 and adds the loop counter from 0 through 51. Update the range and loop limits if the class list changes.
What should I do if the macro cannot find Macro9?
In the VBA editor, check that Macro9 exists in the workbook and is accessible to PrintAllSS1Gold. If it is private or located in a worksheet module, adjust its scope or call as appropriate.
How do I make sure each printed report uses the correct student?
Replace "Report" in the sample code with the sheet containing I232, then manually change I232 and run Macro9 to check that it prints the matching report before running the full loop.




