Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a sheet always visible
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a sheet always visible
Hi
Use Freeze Panes to freeze row(s) where the button is placed. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "avveerkar" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a sheet always visible
You could always put the button in the first few rows, then freeze a pane
(WindowFreeze 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a sheet always visible
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 -- avveerka ----------------------------------------------------------------------- avveerkar's Profile: http://www.excelforum.com/member.php...fo&userid=3033 View this thread: http://www.excelforum.com/showthread.php?threadid=50005 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a sheet always visible
You can program a menu to disappear when you select another sheet and appear when you select the initial sheet. -- Kaak ------------------------------------------------------------------------ Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513 View this thread: http://www.excelforum.com/showthread...hreadid=500050 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a sheet always visible
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a sheet always visible
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a sheet always visible
Yes, in your case I mean sheet. Something like
Private Sub Worksheet_Activate() Application.CommandBars("Formatting").Controls("my Button").Enabled = True End Sub Private Sub Worksheet_Deactivate() Application.CommandBars("Formatting").Controls("my Button").Enabled = False End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "avveerkar" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a sheet always visible
Bob Phillips Wrote: Yes, in your case I mean sheet. Something like Private Sub Worksheet_Activate() Application.CommandBars("Formatting").Controls("my Button").Enabled = True End Sub Private Sub Worksheet_Deactivate() Application.CommandBars("Formatting").Controls("my Button").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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a sheet always visible
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" wrote in message ... Bob Phillips Wrote: Yes, in your case I mean sheet. Something like Private Sub Worksheet_Activate() Application.CommandBars("Formatting").Controls("my Button").Enabled = True End Sub Private Sub Worksheet_Deactivate() Application.CommandBars("Formatting").Controls("my Button").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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a sheet always visible
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a sheet always visible
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" wrote in message ... 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a sheet always visible
Tom Ogilvy Wrote: 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 -- avveerkar ------------------------------------------------------------------------ avveerkar's Profile: http://www.excelforum.com/member.php...o&userid=30338 View this thread: http://www.excelforum.com/showthread...hreadid=500050 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command Button Always Visible in window | Excel Discussion (Misc queries) | |||
XL2003: control button not visible | Excel Programming | |||
Visible Button If..... | Excel Programming | |||
Make Command Button visible only in .xlt? | Excel Programming | |||
how to keep the button visible always in the screen, thanks | Excel Programming |