Chapter 20 · 20.3
A button in your spreadsheet that runs a macro
If you're used to Excel, you'll know the big button on the worksheet: "Create invoice", "Sort list", "Move to archive". Your colleagues don't need to know anything about macros; they just click. In Euro-Office you make a button like this from a shape. You assign a macro to the shape, and clicking the shape runs it.
We'll make a button for the macro Naar archief from Een rij automatisch naar een archiefblad verplaatsen als de status Klaar is, in the order list of Bloemisterij Het Zonnetje.
Step 1: the macro
First create the macro and save it, as described in Een rij automatisch naar een archiefblad verplaatsen als de status Klaar is. A button can only run a macro that is already in the file.
Step 2: draw a shape
- On the Insert tab, click Shape (1).
- Choose a shape that looks like a button, for example the rounded rectangle under Rectangles (2).
- Drag with your mouse to draw a rectangle next to your table, so it doesn't cover the data.
- Type the button text straight away, for example Naar archief. Press Esc when you're done.

Tip: give the button its own colour under Fill in the panel on the right, so your colleagues can see straight away that it's a button and not a comment.
Step 3: assign the macro
- Right-click the border of the shape. If you click in the text, you'll get the text menu; in that case, click the border.
- Choose Assign Macro (1).
- In the Assign Macro window, under Macro name, choose the macro Naar archief and click OK.


Step 4: click
Click anywhere in a cell so the shape is no longer selected. Then click the button once: the macro runs. In our example, the completed orders disappear from the worksheet and are moved to Archief.


Changing the button later
A normal click runs the macro. If you want to change the text, colour or position of the button, right-click its border: the shape is selected without running the macro. You can then drag it or edit the text. Use Assign Macro to assign a different macro.
Pitfalls
- The macro doesn't know where the button is. It works on fixed cells or on the selected cell, not on "the row next to the button". So one button per row makes no sense; create one button for the whole worksheet.
- The macro works on the worksheet that is open. So place the button on the worksheet the macro is meant for.
- Macros disabled? If a colleague has turned macros off, the button does nothing for that colleague. See Using macros safely: macros settings, autostart for colleagues and reuse.
- In Excel, the button is an ordinary shape without an action. Excel can't run a JavaScript macro from it.
What isn't possible
- Buttons in a text document: Assign Macro is only available in spreadsheets. In a text document, you run a macro via View, Macros, Run.
- A button from an Excel file that runs a VBA macro won't work: VBA doesn't run in Euro-Office (see An Excel file with VBA macros: what now?). Convert the macro (see Een VBA-macro zelf omzetten naar JavaScript: de vertaaltabel) and create a new button for it as described above.
