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

long spreadsheet

So what we want to do is this.


Then part 2


Part 3

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

insert button

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.