Home and Learn - Free Excel VBA Course

10.9 Getting the Next and Previous Photos

Ongoing tutorial - First part is here: Part One


The Next Photo button

The question is, How do we move on to the next photo on our spreadsheet? If you think about it, it's quite easy. The only thing you need to do is to move the ActiveCell down one:

ActiveCell.Offset(1, 0).Select

This line move us down one row but keeps us in the same column. What we then need to do is to call three of those Subs we set up:

Call GetTextBoxData

Call GetOptionButtonValue

Call GetImage

The three Subs do all the work of filling out the textboxes, the option buttons and getting a new image.

What we can also do is to check that we haven't gone too far down the rows and ended up on an empty row. We check for this in an If Statement:

If ActiveCell.Value = "" Then


End If

The If Statement checks the ActiveCell for a blank value. If it is blank, we can display an error message and then move the ActiveCell back one:

MsgBox "Last Row"
ActiveCell.Offset(-1, 0).Select
Exit Sub

To move back one row, use a negative number between the round brackets of Offset. We're staying in the same Column so we type a zero after the comma:

Offset(-1, 0)

The Else part of the If Statement is where the calls to the Subs go. Here's the whole of the code for your Next Photo button:

Excel VBA code for the Next Photo button

Run your form and try out your Next Photo button. You should be able to move down through the spreadsheet, displaying all your images. When you get to the end, you'll see the message box telling you that it's the last row.


The Previous Photo button

To move back through the images on the spreadsheet, the code is more or less the same. Here it is:

VBA code for the Previous Photo button

The first line is slightly different, though. It's this:

ActiveCell.Offset(-1, 0).Select

The ActiveCell needs to be moved back one, which we do with Offset:

Offset(-1, 0)

A negative number is used for the Row value, and a 0 is used for the Columns. This means move back one row but stay on the same column.

However, we can't keep going back as we have headers in the first row. The If Statement checks for this:

If ActiveCell.Row = 1 Then

The Row property of ActiveCell is used to test for a value of 1, meaning the first row on the spreadsheet. If it is the first row, we have this:

MsgBox "First Row"
ActiveCell.Offset(1, 0).Select
Exit Sub

On the first line of the code for the button, we moved the ActiveCell back one. If we've moved to the first row then we need to use Offset to move it back to where it was:

ActiveCell.Offset(1, 0).Select

This means move down one row from where the ActiveCell currently is.

The Else part again calls the Subs that do the works of filling out the form with the data from the spreadsheet.

And that's it for the View Photos tab. Run your form and test it out. You should now be able to cycle back and forward through all your photos.


In the next lesson, you'll see how to add a new image to the User Form when we explore Open File dialogue boxes.

< Image Box

Next Lesson: 10.10 Add New Picture >