Macro Button

Buttons can be added to worksheets to let users quickly run scripts without having to use the Automate tab.
At the moment Office Scripts are not supported on the desktop so this button will currently only work in Office Online.

link - techcommunity.microsoft.com/t5/excel-blog/coming-soon-run-office-scripts-with-a-workbook-button/ba-p/2917021 
alt text

Open a browser and login to Office Online.
Create a new Workbook and display the Automate tab.
Click on the Office Scripts drop-down to display the Samples.
Find the sample "Create, Sort and Format a Table".
This sample will create a small table, apply row colouring and then sort the table alphabetically.

alt text

Select this sample and the Code Editor task pane will open.
This task pane will display the Script Details which includes the name and description of the script.
It also tells you when the script was created and who has access.

alt text

Copying a Sample Script

Click on the "More options" button and select "Make a copy".
This will copy the sample script to your personal OneDrive folder.
The script location can be seen below the script name and this will change to MyFiles.

alt text

Renaming a Script

Lets shorten the name of the script to just "Creating a Table".
Click on the "More options" button and select "Rename".
Highlight the existing name and type "Creating a Table" into the text box.

alt text

Press Enter to save this change.
You can also change the script name by just clicking on the name.
Click on the name and change the text to "Press This Button".
Press Enter to save this change.

alt text

Adding the Button

Lets add a button to the active worksheet so this script can be easily run.
Select cell "B2" on the "Sheet1" worksheet.
Click on the "More options" button and select "Add button".
This will insert a button over the current selection on the active worksheet.

alt text

Default Button

The default text will be the name of the script.
The default shape will be a green rectangle with rounded edges.
To select the rectangle use the right mouse button to select.
When the shape is selected a Shortcut Menu (and a small Quick Access Toolbar) will be displayed.

alt text

You must use the "Edit Text" from this shortcut menu to change the text once the button has been created.


Format The Button

Once the shape has been selected it is possible to resize the shape by dragging one of the corners.
You can use the Quick Access Toolbar to change the Style, Fill and Outline of the shape.
You can change the font formatting using the familiar Home tab controls.
You can apply bold, italics and underline as well as changing the colour of the text.

alt text

Running the Script

To run the script, press the button using the left mouse button.
This script will create a new worksheet "Sheet2", create a small table, apply row colouring and then sort the table alphabetically.
The task pane will indicate that the script is running.
If the script is able to run successfully this will be indicated in the task pane.

alt text

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext