When you work on an Excel spreadsheet with several hundred or thousands of records, you have various inconveniences. Among those that stand out is the inconvenience of not knowing where to place the button that automatically executes the macro that you have developed, for a specific purpose.
This task is a bit difficult, since you are always moving the pointer in all directions, often leaving the field of view. Therefore, we will explain to you in a simple way how you can create a floating button for the macro that has been previously developed.
How to Create a Floating Button in Excel
It is necessary to point out that there are different ways to create a floating button that is associated with a macro and that is always visible. Therefore, the most popular alternatives are:
Create a floating button using an AutoShape
This method is one of the most practical and simple, since it allows you to give your button the shape and color you want, then:
- First create your button with an autoshape, to your liking.
- Give the button a name, such as “btnRun.”
- Now, create a macro with the following code inside the sheet you drew the button on. This is because you need to handle the Worksheet_SelectionChange event which will fire every time you change the selection.
- In this code, we are telling Excel that each time we move around the sheet, it calculates the position of the cell and assigns it to the AutoShape named “btnRun”.
- By executing this code you will have already created your floating button that will move throughout the Excel sheet every time we change the active cell. However, I present you whenever:
- With the .Left attribute of the created button, it is indicating that the distance from the left of the screen is equal to the distance of the cell to the right of the active cell.
- With the .Top attribute of the button created, it is being assigned the same distance that the active cell has from the top of the screen.
- The .Height attribute allows you to adjust the height of the active cell. You can remove this line if you have a spreadsheet with many rows of different heights.
Build a floating UserForm by defining it as modeless
This method of making a floating button involves more advanced programming skills, so:
- Open the Vba editor and add a Userform.
- On the Userform paste a button.
- Change the value of Caption in the button properties window.
- Double click on the button to open the module and place the code of a ShowModal. This will allow you to continue working on the sheet despite not having closed the form.
- Program the Workbook_Open event, if you want the button to appear on all sheets, when you open Excel
PrivateSubWorkbook_Open()
UserForm1 Show
End Sub
- If you want the button to appear only on a certain sheet, also program the event in the sheet module
Private Sub Worksheet_Activate()
UserForm1.Show
End Sub
- Finally, if you wish, you can change the size of the UserForm and/or the button according to your needs.
Additional tips for you to modify the code
- If you don’t want the floating button created to move from column to column, you can remove the following line from the general code.
Left = Cells(row, column + 1).Left
- On the contrary, if you would like your button not to move from row to column, you can delete the following line of the general code.
Top = ActiveCell.Top
- If your sheet has many rows with different heights, remove the following line from the general code.
Height = ActiveCell.Height
How does using a floating button in Excel benefit you?
In that you no longer have the need to manually locate the coordinates of the object in Excel that executes a macro, since you will always have it in view and next to the active cell. Therefore, said floating button will follow you depending on the location of the active cell.
In conclusion, if you are interested in continuing to expand your knowledge in Excel and would like to know how to create automatic consecutive numbering using a VBA code macro in Excel, go to the following link.
