To add a button or other interactive tools in your
spreadsheet to activate your macro, use the following procedure.
It wouldn’t be
convenient to need to open the VBA window every time you want to run the macro.
In order to make a more user-friendly spreadsheet, you can add controls in the
spreadsheet to access macro functionality.
-
In your spreadsheet, select the
tool in the
Controls group on the
Developer ribbon tab.
The mouse pointer changes to a crossahair.
-
Click-and-drag a rectangle anywhere in your spreadsheet.
This will form the size and shape of the button, so
something slightly less than 2x2 spreadsheet cells is useful.
The
Assign Macro dialog opens.
-
Select the name of your subroutine from the
Macro name list and then click
OK.
This assigns the button click action to run this
subroutine.
-
Click the new button (labeled Button 1) in your spreadsheet.
The macro runs and the spreadsheet is populated
with the node and member counts.
Note: If you had
previously run the macro from within the VBA Editor window, then it may appear
as though nothing has happened. Select and clear the cell contents and click
the button again to see it in action.
-
Right-click on the button and select
Edit Text to change the button label to
something descriptive.
Click anywhere outside of the button to exit the text
editing state.
The mouse pointer changes to a crossahair.