Button on a sheet always visible

[avveerkar]

Normally the command button I place on a sheet to start a micro rolls
out of the window as I scroll the sheet ( button also moves up as the
rows move up when I scroll the sheet ). Is there any way to lock a
button in a window so that it is always available no matter to what
rows the sheet is scrolled?

A V Veerkar

 

[Arvi Laanemets]

Hi

Use Freeze Panes to freeze row(s) where the button is placed.

 

[Bob Phillips]

You could always put the button in the first few rows, then freeze a pane
(Window>Freeze Panes) below that button.

Or you could add a button to a toolbar.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[avveerkar]

Thanks for your responses. But for design reasons I want to have m
buttons placed at the bottom of the displayed sheet. Cannot lock botto
rows to freeze pane. Yes button in menu bar is an option but it woul
then appear in all sheets of the workbook. I would prefer a comman
button on a sheet, at the bottom and it stays put where it is locate
and not move with scrolling.

A V Veerka

 

[Kaak]

You can program a menu to disappear when you select another sheet and
appear when you select the initial sheet.

 

[Bob Phillips]

You could add code to your workbook to show that button when that workbook
is activated or opened, hide it when deactivated or closed.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[avveerkar]

You could add code to your workbook to show that button when that
workbook
is activated or opened, hide it when deactivated or closed.

--
HTH

Bob Phillips

Dear Bob,

Can you clarify? I code my workbook ( you mean worksheet ) to show
button when it is activated - how does that help? Sorry, I am missing
the point. As I select the sheet, my code will display the button where
I want it but how do I lock it in that position? It will still move up
when I scroll the sheet. Are you suggesting that I should deactivate
the sheet and activate it again so that the button is re-displayed (
by, let us say, selecting other sheet and then coming back to this
sheet )? I still don't understand how this will help.
I appreciate your spending time on this.
A V Veerkar

 

[Bob Phillips]

Yes, in your case I mean sheet. Something like

Private Sub Worksheet_Activate()
Application.CommandBars("Formatting").Controls("myButton").Enabled =
True
End Sub

Private Sub Worksheet_Deactivate()
Application.CommandBars("Formatting").Controls("myButton").Enabled =
False
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[avveerkar]

Yes, in your case I mean sheet. Something like

Private Sub Worksheet_Activate()
Application.CommandBars("Formatting").Controls("myButton").Enabled =
True
End Sub

Private Sub Worksheet_Deactivate()
Application.CommandBars("Formatting").Controls("myButton").Enabled =
False
End Sub


--
HTH

Thanks Bob. What you are suggesting is to have a button on Tools Bar
which will be only made " Enabled True " when my particular sheet is
activated. That way it will be hidden when any other sheet is
displayed. But is it just not possible to put a button ON THE SHEET
and see that it does not move up as the sheet is scrolled down? My
application will be much better if I can put the button somewhere at
the bottom which will not move up as I scroll the sheet. My application
requires me to scroll down to a particular row, select the cell of
interest and then press a button to modify the cell selected. Since I
always scroll down to look for particular cells, my mouse pointer is
always at the bottom right corner on scroll bar. I would like to have
the button placed at the bottom right corner of the sheet where I can
easily go and click and come back to the scroll bar.
A V Veerkar

 

[Tom Ogilvy]

Put in a split screen with the small area at the bottom. Scroll you button
into view in the bottom pane.

Window=>split, then drag the verticle bar to one side and drag the
horizontal bar toward the bottom

 

[avveerkar]

Put in a split screen with the small area at the bottom. Scroll you
button
into view in the bottom pane.

Window=>split, then drag the verticle bar to one side and drag the
horizontal bar toward the bottom

--
Regards,
Tom Ogilvy

Thanks Tom. That is good work around I can use. Small drawback though
is I will always have those Split Bars seen on the sheet which is a
little uncomfortable. I was wondering if it is possible to fix the
position of the button static in a window by appropriately coding. To
draw a button we need to specify it coordinates ( top edge and left
edge position ). Is it possible to sense scroll position of the sheet
and then redraw the button accordingly so that it would appear to be
static while scrolling.

A V Veerkar

 

[Tom Ogilvy]

There is no event associated with a window Scroll. You could use the
selection change event which will work more often than required, but should
do the job.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static Oldrange As Range
Dim rng As Range, rng1 As Range

If Oldrange Is Nothing Then
Set Oldrange = ActiveWindow.VisibleRange
Exit Sub
End If
If ActiveWindow.VisibleRange.Address <> _
Oldrange.Address Then
Set rng = ActiveWindow.VisibleRange
Set rng1 = rng(rng.Count).Offset(-2, -2)
CommandButton1.Top = rng1.Top
CommandButton1.Left = rng1.Left
End If


right click on the sheet tab and select view code. Put in code like the
above in the resulting module.

 

[avveerkar]

There is no event associated with a window Scroll. You could use the
selection change event which will work more often than required, but
should
do the job.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static Oldrange As Range
Dim rng As Range, rng1 As Range

If Oldrange Is Nothing Then
Set Oldrange = ActiveWindow.VisibleRange
Exit Sub
End If
If ActiveWindow.VisibleRange.Address <> _
Oldrange.Address Then
Set rng = ActiveWindow.VisibleRange
Set rng1 = rng(rng.Count).Offset(-2, -2)
CommandButton1.Top = rng1.Top
CommandButton1.Left = rng1.Left
End If


right click on the sheet tab and select view code. Put in code like
the
above in the resulting module.

--
Regards,
Tom Ogilvy


Thanks a million Tom. That should do it.
Thanks a lot to Bob also. I am closing this thread now.

A V Veerkar