Using VBA buttons to move in a sheet

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 entry sheet like a 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.

part1

Then part 2

part2

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 window that is open 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 buttons.

For this use this menu

insert button

Position the buttons, and the window will open

button

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.

button

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.