Excel Macro Buttons on Floating Form

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.

userformbuttons04

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.

userformbuttons01

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.

userformbuttons02

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.

_____________________

userformbuttons03a

userformbuttons03b

_______________

One thought on “Excel Macro Buttons on Floating Form”

  1. 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

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.