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

_______________

You may also like...

7 Responses

  1. Jerome says:

    Brilliant.

    Just what I wanted.

    Thanks Debra.

    Jerome

  2. Freddie Henderson says:

    I have one issue with this. I have the following for one of the buttons and it is not working.

    Private Sub cmdNav01_Click()
    Range(“A2”).Select
    ‘ ActiveSheet.Paste
    ActiveSheet.Application.SendKeys “^v”
    End Sub

    Basically I have some text from another source in the clipboard. I then select A2 and paste it.

    If I run as a macro I have to use the ^v command as the dates are brought in as American by default from the tab limited source. So I get a column of mixed American and UK date formats (eg 01/05/16 means 1st May 2016, but it will read 5th Jan in the spreadsheet. 28/05/16 cannot be misinterpreted as there are not 28months, so correctly identified as 28 May 2016
    Normal Paste from the keyboard interprets the dates correctly, but ActiveSheet.Paste results in this misunderstanding.
    Using ^v in the macro correctly interprets the dates.

    HOWEVER.
    ActiveSheet.Paste works on the floating form when I press the button, and it pastes my clipboard, but with american dates in places.
    but ^v does not do anything, it is like the macro stopped working after the selection of A2

    Any thoughts?

  3. Freddie Henderson says:

    Found the solution myself. I need to move the focus off the userform back onto Excel.

    New command is:

    Private Sub cmdNav01_Click()
    AppActivate (“Microsoft Excel”)
    Range(“A2”).Select
    ActiveSheet.Application.SendKeys “^v”
    End Sub

  4. kid says:

    is there a way to only show the floating form on the active workbook worksheet rather then if you have two workbook open, it shows on any of them.

    • @kid, you could add code to the workbook Activate and Deactivate events:

      Private Sub Workbook_Activate()
      frmButtons.Show
      End Sub

      Private Sub Workbook_Deactivate()
      frmButtons.Hide
      End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *