In the olden days (Excel 2003 and earlier), there were toolbars, and you could “float” those, and position them anywhere over the worksheet. You could also create custom toolbars, and add a few commands and macros to those. They could be moved around the worksheet too.
In the newer versions of Excel, those floating toolbars aren’t available, but you can create something similar, based on a UserForm. Build your own, or download my example, and customize it.
Easy Access to Buttons
In Excel 2007 and later, to make it easy for people to run macros, or move around in a workbook, you can add buttons in a frozen pane, at the top of the worksheet. Or, create a custom tab on the Ribbon, and put the commands there.
However, that can be a long way to travel, if you’re working on a big monitor, or doing a presentation.
To position the macro buttons where you need them, you can create a floating form with macro buttons. Then, move it anywhere on the screen, so the buttons are close to the place that you need them.
Or, if you like to right-click, you can use Doug Glancy’s free MenuRighter add-in, and put your favourite commands there.
Create a UserForm
To create a floating form, you can insert a UserForm in the workbook, and have it open automatically when the file opens. There are instructions for creating a UserForm on my website, if you haven’t built one before.
To create a floating form:
- Insert a UserForm in the workbook
- Change the form’s ShowModal setting to False, so you’ll be able to use the worksheet while the form is open.
- Then, add a few command buttons, and set those to run navigation macros, or other types of macros.
Here is a screen shot of the small and simple form that I built.
Open the Form Automatically
In the workbook module, use the Workbook_Open event to show the UserForm, so it appears as soon as the workbook opens.
Here is the code that I added to the ThisWorkbook module in my example – the UserForm is named frmButtons.
Private Sub Workbook_Open() frmButtons.Show End Sub
Use the Buttons
After the form opens automatically, it can be positioned anywhere on the screen, for easy access.
I also added a Buttons sheet to the sample file, and you can click “Show the Buttons” on that sheet, to open the floating form again, if you’ve closed it.
Download the Sample File
To see how the UserForm works, you can download the sample file from my Contextures website. On the Excel Sample Files page, go to the UserForms and VBA section, and look for UF0032 – UserForm With Macro Buttons. The zipped file is in xlsm format, and you can enable macros when you open the file.
To adapt the file for your workbook, change the captions of the buttons, and change the code on the command buttons, so they run your macros. You can add more buttons, or remove some of the existing ones.
_____________________
_______________
Great stuff,
But I have a 1 question.
The keyboard control stays in the Button form. If you use the arrow keys you see the buttons get highlighted.
I first have to click with the mouse into the sheet to be able to type anything in the sheet.
Is there a way to prevent that keyboard control stays in the button form?
Thanks