When you work on an Excel sheet with several hundred or thousands of records, you have several drawbacks . Among those that stand out the annoyance 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, leaving the field of vision many times. Therefore, we will explain in a simple way how you can create a floating button for the macro that has been previously developed.
How Can I Create or Make a Floating Button in Excel – Step by Step
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 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 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 ” btnExecutar “.
- Now, create a macro with the following code inside the sheet where you drew the button. This is because you must handle the Worksheet_SelectionChange event that will fire every time you change the selection.
- In this code, we are telling Excel that every time we move through the sheet, calculate the position of the cell 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 of making a floating button involves more advanced programming knowledge, 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 ()
UserForm1 Show
End Sub
- 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 ()
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 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 following line from the general code .
Height = ActiveCell.Height
How does using a floating button benefit you in Excel?
In that you no longer have the need to be manually locating the coordinates of the object in Excel that runs a macro, since you will always have it in view and adjacent 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 further expanding your knowledge in Excel and would like to know how to create an automatic consecutive numbering using a VBA code macro in Excel , enter the following link.
