Using VBA buttons to move in a sheet
Sometimes you have a very long sheet and would like to move quickly and
precisely from one area to the other.
Using VBA buttons to move in a sheet will allow you to go from chapter to
chapter or entry to entry in a long sheet.
For example if you have a very long business plan or a
long table that you have to browse regularly and you wish to scroll down
always to the same positions, then use buttons.
Here an example of a long sheet
So what we want to do is this.
Then part 2
Part 3
You can add more parts.
Now to create the buttons go into the
developper menu
(press the link to learn how to activate the developer ribbon on the screen)
See how the chapters are named (Part 1, Part 2, Part 3). You will need these
reference for the coding.
Open the VBA editor with ALT-F11.
In the VBA window that
just opened, copy the following code as many times as you have
buttons.
Rename each time the title of the macro (here FindPart1, FindPart2, ....)
and replace Part 1 by Part 2, Part 3, ...
Sub FindPart1()
'find next part
Cells.Find(What:="Part
1").Activate
'moves down
ActiveWindow.ScrollRow = Selection.Row
'offset the entry point
Selection.Offset(RowOffSet:=1,
ColumnOffset:=1).Select
End Sub
Now go back in the sheet and create the VBA buttons.
For this use this menu
Position the buttons, and the window will open
Select the macro you want it to use. Is it part 1,2 or 3
Then you have a button that you must rename that appears on the screen.
Right click on it, then left click on the text to edit the text and rename
it.
Once you have all t he buttons on the screen. That's it.
Good luck.
Download the sheet from
here.
Please Tweet, Like or Share us if you enjoyed.