Home and Learn - Free Excel VBA Course
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:
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:
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
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"
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:
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:
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.
To move back through the images on the spreadsheet, the code is more or less the same. Here it is:
The first line is slightly different, though. It's this:
The ActiveCell needs to be moved back one, which we do with Offset:
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"
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:
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.