Fix Position of Form Control Button on Worksheet

How to fix the position of a form control button on a worksheet automatically using VBA. Watch the Excel VBA training video below:

Fix Position of Form Control Button on Worksheet automatically with VBA

Often we insert shapes, ActiveX controls and form controls on our worksheet and assign a macro to them. This makes it easier for us to run a macro by clicking on the shape, ActiveX control like a command button or form control button. However, we also notice that the controls shift their positions or disappear entirely when we execute a macro assigned to these controls. How can we ensure that our shapes and controls on the worksheet remain in a fixed position where we wish to have them? We can do this manually by right-clicking on the shapes or controls and selecting ‘Don’t move or size with cells’. But many a times this does not work and the controls become larger or smaller. Therefore, we need to write a macro or VBA code to ensure that the controls remain in a fixed position or stay put. Below is the code to keep the form control buttons in place:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With ActiveSheet.Shapes(“Button 1”)
.Top = ActiveWindow.VisibleRange.Top + 30
.Left = ActiveWindow.VisibleRange.Left + 400
.Placement = xlFreeFloating
End With

With ActiveSheet.Shapes(“Button 2”)
.Top = ActiveWindow.VisibleRange.Top + 30
.Left = ActiveWindow.VisibleRange.Left + 600
.Placement = xlFreeFloating
End With

End Sub

What if we use an ActiveX control? The following macro will solve the problem:

With ActiveSheet.OLEObjects(“CommandButton1”)
.Top = ActiveWindow.VisibleRange.Top + 100
.Left = ActiveWindow.VisibleRange.Left + 400
.Placement = xlFreeFloating
End With

Of course, we have to change the name of the controls according to our assignments.

What if we use a shape like an oval and assign it to a macro? The VBA code below will ensure that the shape stays in its fixed position:

With ActiveSheet.Shapes(“Oval 1”)
.Top = ActiveWindow.VisibleRange.Top + 200
.Left = ActiveWindow.VisibleRange.Left + 400
.Placement = xlFreeFloating
End With

If you do not wish to work with a macro then you can add the button to the quick access toolbar (QAT).

Fix Position of Form Controls, ActiveX controls and Shapes on Worksheet

Download a sample file for practice:

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.