Microsoft Office Tutorials and References
In Depth Information
KEEP A BUTTON IN VIEW
KEEP A BUTTON IN VIEW
Challenge: You have a worksheet that contains 10,000 rows of data. As people
scroll through the workbook, you want the macro button to always be in view.
Solution: One option is to use Freeze Panes to keep a few rows visible at the
top of the screen and place the button in that area. If you are in Excel 2003,
you could use a custom ﬂ oating toolbar for this. In any recent version of Excel,
you could use a modeless user form to hold the button. Another method, as
described in this topic, is to use the worksheet SelectionChange macro to
reposition the button at the top of the screen.
Add a forms button to your worksheet. If you use the Forms dialog, the button
will have a name such as Button 1 . If you use an ActiveX control, it will
have a name such as CommandButton1 . This concept works with any other
control (such as a combo box). Simply replace the name of the control in the
ActiveSheet.Shapes("Button 1") line of code.
Access the code pane for your worksheet by right-clicking the tab name in
Excel and choosing View Code. Paste the following code into the code pane:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ScrollRw As Long
Dim ScrollCol As Integer
ScrollRw = ActiveWindow.ScrollRow
ScrollCol = ActiveWindow.ScrollColumn
With ActiveSheet.Shapes("Button 1")
.Top = Cells(ScrollRw, ScrollCol).Top
.Left = Cells(ScrollRw, ScrollCol).Left
As long as the user uses the keyboard to navigate the worksheet, the button
will reliably stay in the top left of the window. Using Page Down, Page Up,
Alt+Page Down, and Alt+Page Up to scroll works best. If a user scrolls by
using the arrow keys, the button annoyingly dance around. If a user scrolls
using the wheel mouse or the scrollbars, the button disappears until the user
clicks inside the worksheet.
Additional Details: The button has a Top property and Left property. These
indicate the distance from A1 in pixels or points. The macro ﬁ nds the top row in
the visible window by using ActiveWindow.ScrollRow . The macro ﬁ nds the
left column in the visible window by using ActiveWindow.ScrollColumn .
These two lines might tell you that the top-left cell in the window is G501.
Interestingly, you can learn the distance from the top-left corner of A1 to cell
G501 by using Range("G1").Top and Range("G1").Left.