Keep A Button In View

June 18, 2021 - by Bill Jelen

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 floating 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:

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 finds the top row in the visible window by using ActiveWindow.ScrollRow. The macro finds 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.

The lines inside the With block assign the Top property for the button to be the same as the Top property for the cell at the top-left corner of the visible window.

As you scroll through the worksheet, the button stays at the top of the window (Figures 144 and 145).

Figure 144. If you scroll down, the button moves.

Figure 145. The button moves as you scroll through the worksheet.

Summary: You can use a macro to keep a button visible in a worksheet.

Source: http://www.mrexcel.com/forum/showthread.php?t=86319

Title Photo: James Sutton on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.