May 01, 2004

Quick Navigation, Part 2

Last time we looked at Hyperlinks. Here’s something extra I always do for really big files.

I always find the 2 places I most want to go are to the top of the file and the bottom of the file. So I’ve made 2 buttons like below using the oval AutoShape(You can use any shape etc you want to. I’ve also tried to make them more attractive by changing the color and also adding a shadow effect.

Believe it or not, I think I’ll start at the bottom. Scroll down to the very bottom of the file. In the case of the below picture, I’ve selected cell B102 then entered FileBottom in the Name Box.

Scroll back to the Scroll Down button and right click it to select Hyperlink. Select the Place in This Document tab and then the name you entered, FileBottom. From now on pushing the Scroll Down button will get to the bottom of things real fast.

Now, let’s try something else. Open your Visual Basic Editor and paste the following code. (Try pushing Ctrl + F11)

Sub ScrollUp()
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End Sub

Go back to the Excel File and right click the Scroll Up button to select Assign Macro and choose the ScrollUp subroutine you’ve just pasted. You can now use the Scroll Up button to get to the top in a hurry.

One more thing, you’ll notice when you scroll down the bottom that the Scroll Up and Scroll Down buttons are not visible.

No problem. Click the Window Menu and then Freeze Panes. I recommend that you do this just under the titles of the file. This will enable you to use the buttons as well see the titles no matter how far you scroll down.

Of course, you can change any of these settings to suit what’s best for you. For example, you could adapt the ScrollUp macro to go somewhere else besides the top left corner of the book by changing the values of either ActiveWindow.ScrollRow = 1 or ActiveWindow.ScrollColumn = 1.

I hope these tips were useful. It’s time for bed.

This page is powered by Blogger. Isn't yours?