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
Hi
Use Freeze Panes to freeze row(s) where the button is placed.
--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )
"avveerkar" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> avveerkar
> ------------------------------------------------------------------------
> avveerkar's Profile:
> http://www.excelforum.com/member.php...o&userid=30338
> View this thread: http://www.excelforum.com/showthread...hreadid=500050
>
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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> avveerkar
> ------------------------------------------------------------------------
> avveerkar's Profile:
http://www.excelforum.com/member.php...o&userid=30338
> View this thread: http://www.excelforum.com/showthread...hreadid=500050
>
Thanks for your responses. But for design reasons I want to have my buttons placed at the bottom of the displayed sheet. Cannot lock bottom rows to freeze pane. Yes button in menu bar is an option but it would then appear in all sheets of the workbook. I would prefer a command button on a sheet, at the bottom and it stays put where it is located and not move with scrolling.
A V Veerkar
You can program a menu to disappear when you select another sheet and appear when you select the initial sheet.
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" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks for your responses. But for design reasons I want to have my
> buttons placed at the bottom of the displayed sheet. Cannot lock bottom
> rows to freeze pane. Yes button in menu bar is an option but it would
> then appear in all sheets of the workbook. I would prefer a command
> button on a sheet, at the bottom and it stays put where it is located
> and not move with scrolling.
>
> A V Veerkar
>
>
> --
> avveerkar
> ------------------------------------------------------------------------
> avveerkar's Profile:
http://www.excelforum.com/member.php...o&userid=30338
> View this thread: http://www.excelforum.com/showthread...hreadid=500050
>
[QUOTE=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
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
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" <[email protected]> wrote in
message news:[email protected]...
>
> Bob Phillips Wrote:
> > 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
>
>
> --
> avveerkar
> ------------------------------------------------------------------------
> avveerkar's Profile:
http://www.excelforum.com/member.php...o&userid=30338
> View this thread: http://www.excelforum.com/showthread...hreadid=500050
>
[QUOTE=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
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
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
"avveerkar" <[email protected]> wrote
in message news:[email protected]...
>
> Bob Phillips Wrote:
> > 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
>
>
> --
> avveerkar
> ------------------------------------------------------------------------
> avveerkar's Profile:
http://www.excelforum.com/member.php...o&userid=30338
> View this thread: http://www.excelforum.com/showthread...hreadid=500050
>
[QUOTE=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
--
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
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
"avveerkar" <[email protected]> wrote
in message news:[email protected]...
>
> Tom Ogilvy Wrote:
> > 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
>
>
> --
> avveerkar
> ------------------------------------------------------------------------
> avveerkar's Profile:
http://www.excelforum.com/member.php...o&userid=30338
> View this thread: http://www.excelforum.com/showthread...hreadid=500050
>
[QUOTE=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.
--
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks