When you work on an Excel sheet with several hundred or thousands of records, you are have various drawbacks. Among those that stand out the annoyance of not knowing where to place the button that run the macro automatically that you have developed, for a specific purpose.
This task is a bit difficult, since you are always moving the pointer in all directions, leaving the visual field many times. Therefore, we will explain in a simple way how you can create a floating button for the macro that has been developed previously.
How to create a Floating Button in Excel
It should be noted that there are different ways to create a floating button that is associated with a macro and that it is always visible. Therefore, the most popular alternatives are:
You may also be interested in:
This method is one of the most practical and simple, since it allows you to give the shape and color you want to your button, 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 where you drew the button. This is because you must handle the event Worksheet_SelectionChange which will fire every time you change the selection.
- In this code, we are telling Excel that every time we move through the sheet, calculate cell position and assign it to the autoshape called «btnEjecutar».
- 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 always present you that:
- With the .Left attribute of the created button, you are indicating that the distance from the left of the screen is equal to the distance of the cell that is to the right of the active cell.
- With the .Top attribute of the created button, you are assigning the same distance that the active cell has from the top of the screen.
- The .Height attribute allows you to adapt the height of the active cell. You can remove this line if you have a spreadsheet with many rows of different height.
Make a floating UserForm defining it as modeless
This method for craft a floating button implies more advanced knowledge of programming, then:
- Open the Vba editor and add a Userform.
- On the Userform paste a button.
- Change the Caption value in the button’s 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.
- Schedule the Workbook_Open event, if you want the button to appear on all sheets, when you open Excel
Private Sub Workbook_Open ()
- If you want the button to appear only on a certain sheet, also schedule the event in the sheet’s module
Private Sub Worksheet_Activate ()
- 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 him floating button created is moved column, you can remove the following line of the general code.
Left = Cells (row, column + 1) .Left
- On the contrary, if you want your button not to move from row to column, you can remove the following line of the general code.
Top = ActiveCell.Top
- If your sheet has many rows with different heights from each other, remove the next line from the general code.
Height = ActiveCell.Height
In that you no longer have the need to be manually locating the coordinates of the object in Excel that executes a macro, since you will always have it in view and adjacent to the active cell. Therefore said floating button It 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 an automatic consecutive numbering using a macro of VBA code in Excel, enter the following link.
I'm sorry this content was not useful for you!
Let me improve this content!
Tell me, how can I improve this content?
You may also be interested in: