Excel like a finance pro.
VBA · User interaction
Assigning a macro to a button
Runs a macro from a shape or form control on the sheet.
When to use it
A shape or form-control button with a macro assigned gives users a one-click way to run it without knowing about macros.
The code
- Code
Insert a shape, right-click, Assign Macro
Worked examples
Shape button
Insert > Shapes, draw a rectangle, right-click > Assign Macro, pick the Sub → Clicking the shape runs the macro
Shapes look better than form buttons.
Form control button
Developer > Insert > Button (Form Control), draw, assign → A classic grey button
Quick and functional.
The macro
Sub RefreshAll_Click() ThisWorkbook.RefreshAll MsgBox "Refreshed" End Sub → The macro behind a Refresh button
Keep button macros short.
Worth knowing
- Name the shape (Name Box) so code can find it.
- Set "Don't move or size with cells" so it stays put.
- Application.Caller returns the shape name inside the macro.
Where it goes wrong
- Buttons on protected sheets need the macro to unprotect and reprotect.
- ActiveX buttons cause problems across versions; use form controls or shapes.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.