What happened to my button?
I recently had a customer ask how he could freeze objects, in this case a set of buttons, so that they didn't disappear as he scrolled through his Excel spreadsheet. This sounds like it would be a common request and have a topic in Excel help. However, that's not the case. Help does explain how you can freeze rows or columns when scrolling by using the Freeze Panes command on the Windows menu so we can put that feature to use in this case.
One method to anchor an object (button) is to insert a blank row at the top of the spreadsheet and move the button so that it covers the row. Next click Freeze Panes on the Windows menu. Then move your cursor along the left side of the screen and reduce the height of the pane to to the exact height of your object. You can also lock objects along the left side of the screen. Click a column letter, and then click Freeze Panes on the Window menu. Now as you scroll to the right, any objects along left side of the .spreadsheet remain fixed. To freeze objects in both a row and a column, click the cell just below and to the right of the row and column you want to freeze and then click Freeze Panes on the Window menu.
An alternative technique is to create a floating toolbar. For example, the following VBA code first checks to see if the named custom toolbar exists, and if not, creates a floating toolbar which stays visible when you scroll through the spreadsheet. Do this in the .Workbook_Open event and delete them in the .Workbook_BeforeClose event. This way you are sure you have full control over the menus and that they are pointing to the right code when they execute.:
Dim cbar As Office.CommandBar
Dim bExists As Boolean
bExists = False
For Each cbar In Application.CommandBars
If cbar.Name = "Custom Toolbar" Then bExists = True
Next
If Not bExists Then
Set cbar = Application.CommandBars.Add("Custom Toolbar", _
msoBarFloating, False, True)
cbar.Visible = True
End If
And yet another way is to use the Attach command in the the Tools=>Customize dialog in the Toolbars tab. This way, when your file is opened, if the toolbar does not exist, it is created from the toolbar attached to the file. It is still a good idea to remove the toolbar when you close the workbook by using the .Workbook_BeforeClose event.
You can add similar freeze pane functionality when working with large tables in Access. You can freeze columns in Datasheet view and continue to horizontally scroll through records. To freeze a column, click on the field's name in Datasheet view to select the column. Then, choose Format/Freeze Columns from the menu bar. Access moves the column all the way to the left of the view and freezes it. If you freeze additional columns, they are moved to the right of the last frozen column. To .return the view to normal, select Format/Unfreeze All Columns from the menu bar. Note that there is one catch to using this method. Although the columns are no longer frozen, Access doesn't return them to their original positions within the Datasheet view.
And speaking of working with alternate screen displays, in VBA, there are often times when it's helpful to view non-adjacent code in a module. For example, a calling routine and the called routine. However, continually scrolling back and forth through your code can be a real nuisance. Fortunately, you can easily split the module window into two scrollable panes. Simply drag the split box (the small rectangle that's directly above the vertical scroll bar on the right-hand side of the code window) to where you want the split made. To remove the split, just double-click on the split line.