Button to stay on screen as you scroll

[Guest]

I would like for a command button to stay on sheet as I scroll, to allow the
option of clicking it wherever I scroll

 

[Tom Ogilvy]

put the button in row 1

select row 3 and do Window=>Freeze Panes

To undo the Frozen Pane, do Window => UnFreeze Pane

 

[Markus Scheible]

Hi Tom,

select row 3 and do Window=>Freeze Panes

btw: is there any possibility without freezing panes? I
thought about that too and also just made out the
freezing... but one should also can lock the button,
shouldn't?

Best

Markus

 

[Guest]

Is there no code like OnScroll or something that everytime you scroll you can
get the nwe coordinates of the page and feed these into the command buttons
positions??

Don't know if there is code for this, but it'd be worth investigating

 

[Tom Ogilvy]

I am not aware of any setting/property that would make it float.

If it is a button, I would use a floating commandbar, but that wasn't the
request.

 

[Dave Peterson]

I think Tom's suggestion is the best (but I'd freeze column A, too <bg>).

But if you want...

(Saved from a previous post)

It sure sounds like you're describing a toolbar to me. (I think it would be
easiest to just let it float (don't dock it to the top of the application
window).)

But if you want, maybe something like this:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myShape As Shape

Set myShape = Me.Shapes("Button 1")

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
myShape.Top = .Top
myShape.Left = .Left
End With

End Sub

Rightclick on the worksheet that should have this behavior. Select view code
and paste this into that codewindow.

If you did window|freeze panes, so that row 1 is always visible, you may want to
change:

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
to
With Me.Cells(1, ActiveWindow.ScrollColumn)

(or whatever row you want the button in).

This routine moves the button when the selection changes. If the user moves the
screen using the scroll bars, then it won't show up until they select a range.