Button to stay on screen as you scroll

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

put the button in row 1

select row 3 and do Window=>Freeze Panes

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

--
Regards,
Tom Ogilvy

"fred" <fred@discussions.microsoft.com> wrote in message
news:EAAFEAD0-EB2A-40EB-969A-378B27E21121@microsoft.com...
> I would like for a command button to stay on sheet as I scroll, to allow
the
> option of clicking it wherever I scroll

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

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

"fred" wrote:

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

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.

--
Regards,
Tom Ogilvy

"Markus Scheible" <anonymous@discussions.microsoft.com> wrote in message
news:179e01c50f6b$7afaa0d0$a601280a@phx.gbl...
> 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

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.


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

--

Dave Peterson