How to Display a Spinner When a VBA Macro Is Running

Create a macro in Excel to increment a value.

The best way to allow a user increase or decrease a value in your Microsoft Excel application when a macro is running is by using a spin button. A macro is commonly used to record a sequence of actions that can later be executed by executing the macro manually or by clicking a button. A macro is created through the use of Visual Basic for Applications (VBA), a computer programming language used in Microsoft Office applications to automate routine tasks such as displaying a spinner.

Instructions

1. Launch Microsoft Excel, click the "Developer" tab and click the "Insert" icon on the "Controls" pane. Click "Button" and click the spreadsheet to add a new command button. Click "Spin Button" below "ActiveX Controls" and add it to the spreadsheet.

2. Right-click the spinner and click "Properties" to display the property window. Choose "False" next to the "Visible" property. Click the "Macros" icon and type "IncrementValues" below "Macro name." Click the "Create" button to create a new macro.

3. Copy and paste the following code to create a "While...Loop" and update the spreadsheet with values every time the user clicks the spinner button:

   Sheet1.SpinButton1.Visible = True

   Do While (counter <> 10)

   Range("A1").Select

   Range("A1").Value = counter

   Range("A2").Select

   Range("A2").Value = "Macro running..."

   Application.Wait Now + TimeValue("00:00:02")

   DoEvents

   Loop

   Range("A1").Select

   Range("A1").Value = ""

   Range("A2").Select

   Range("A2").Value = "Macro ended."

   Sheet1.SpinButton1.Visible = False

   counter = 0

4. Add the following line of code in the very top of your module to create a global variable:

   Global counter As Integer

5. Switch back to Microsoft Excel and right-click "Button 1." Click "Assign Macro" and click "IncrementValues." Click "OK."

6. Right-click the spinner button and click "View Code" to open a VBA code module. Copy and paste the following code to increment or decrement the global variable value:

   Private Sub SpinButton1_SpinDown()

   counter = counter - 1

   End Sub

   Private Sub SpinButton1_SpinUp()

   counter = counter + 1

   End Sub

7. Switch back to Microsoft Excel and click "Button 1" to start running the macro and display the spinner control. Click the up arrow of the spinner button to increment or the down arrow to decrement. The macro will hide the spinner and stop running when the global variable equals 10.

References

• Microsoft Office: Add a Spin Button to a Worksheet
• Microsoft Office: Create a Macro