April 30, 2022

Create Floating Button

How to create a floating button form control on an Excel worksheet with an event handler macro. Watch the video below:

Create floating button form control on Excel worksheet

We can easily assign a macro to a button form control on an Excel worksheet. Can we create a floating button to which a macro can be assigned so that if we move left, up, down or right the button moves along and is always visible? We will have to create an event handler in our worksheet so that whenever we select a cell in the worksheet the button remains visible next to the cell. We right click the worksheet in which we wish to have the button, select view code and write the macro as shown below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet.Shapes(“Button 2”)
.Top = Target.Offset(1, 1).Top
.Left = Target.Offset(1, 2).Left
End With
End Sub

Floating Form Control Button

Of course we can also consider using a short-cut key for the button or assigning it to a button on the Quick Access Toolbar. Macros can be assigned to shapes also.

The floating button is attached to the following macro:

Sub CalculateTax()

Dim i As Long, lastrow As Long
lastrow = Application.WorksheetFunction.CountA(Sheet1.Range(“A:A”))
MsgBox “The last row used is ” & lastrow
For i = 2 To lastrow
Cells(i, 4) = Cells(i, 3) * Cells(i, 2)
Cells(i, 5) = Cells(i, 4) * 0.12
Next i

End Sub