Moving a button on worksheet

[Jock]

Is it possible to make a button "float" so that it's visible at all times
when the user scrolls or uses page down? If not, I could freeze panes but I
was just wondering.

Thanks.

 

[JP]

Hi,
There doesn't seem to be a "Window Scroll" Event so you might be stuck
with your current solution.


HTH,
JP

 

[Frederik]

Hello

instead of a button, you could assign a key shortcut to the macro...

 

[Jock]

Fair enough. Thanks for the reply tho.
--
Traa Dy Liooar

Jock

Hi,
There doesn't seem to be a "Window Scroll" Event so you might be stuck
with your current solution.


HTH,
JP

 

[Ian]

It's not possible in the way you are thinking (ie it always stays in the
same relative position in the window), but you can make it stay in the same
position relative to the active cell. This is a button from the Control
toolbox, not Forms.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Row > 65530 Or ActiveCell.Column > 252 Then
CommandButton1.Top = ActiveCell.Offset(-3, 0).Top
CommandButton1.Left = ActiveCell.Offset(0, -3).Left
Else
CommandButton1.Top = ActiveCell.Offset(1, 0).Top
CommandButton1.Left = ActiveCell.Offset(0, 1).Left
End If
End Sub

This normally positions the top left corner of the button with the bottom
left corner of the active cell. If the active cell is row 65530 or greater,
or column IR or beyond, the button repositions to 3 cells up and 3 cells
left of the active cell to avoid the button resizing when it reaches the
edge/bottom of the sheet, and to avoid an error in the code when in the last
row or column.

The drawback with this is that when the active cell is either the bottom
visible row or the rightmost visible column, the button will be outside the
visible area.

 

[Ian]

instead of a button, you could assign a key shortcut to the macro...

Or assign the macro to a toolbar button

Ian