1.8 Resize
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
Else
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:
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:
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.
Next Lesson: 10.10 Add New Picture >