1.8 Resize
Home and Learn - Free Excel VBA Course
10.8 Loading Images into ImageBoxes
Ongoing tutorial - First part is here: Part One
Return to your form and click on your image box. Now look at its properties on the left. One if these is called Picture:
If you were to click the grey button with the three dots in it, you'd see a dialogue box appear allowing you to choose a picture for your image box.
You can, however, write code to load a picture into an image box. You do it with the LoadPicture method.
File Paths
NOTE: We've had to put spaces in our file paths below as the lines were too long. So careful if you're copying and pasting!
Before we can load an image, we need to construct a file path. We need to do this because they only thing we have at the moment is image names in cells on the spreadsheet. The LoadPicture method requires a full file path. So you need something like this:
C:\Users\Owner\Documents\images\ london_eye.jpg
and not just this:
london_eye.jpg
We could, of course, have entered the full file path in the spreadsheet cells, which would have been easier. But this is not recommended. Simply because if you move your images to a different location then the file references wouldn't work.
Another technique is to enter a partial file path in a cell on your spreadsheet. So in cell H1, for example, we could have this:
C:\Users\Owner\Documents\images\
We could then get this file path and join it together with the image name. Like this:
FilePath = Worksheets(3).Range("H1").Value
ImageName = ActiveCell.Value
FullPath = FilePath & ImageName
This would work fine, and should be considered as an option. What we'll do, however, is to use ThisWorkbook.Path. What this does is to get you the file path where the current workbook is. We have saved our workbook to a folder called sheets. So the file path reference might be:
C:\Users\Owner\Documents\vba\ projects\sheets\
Notice that this doesn't get us the image name. If you remember, we have a sheets folder and an images folder:
By using ThisWorkbook.Path we'd only get a path to the workbook, which is in the sheets folder. We'd need to go up one folder from this to reference the image folder. (There is no relative file referencing, in case you're wondering.) The file path to the image folder, then, is this:
C:\Users\Owner\Documents\vba\ projects\images
In other words, we need to chop off the sheets folder at the end of "C:\Users\Owner\Documents\vba\ projects\sheets\" and add images instead. We can do this with a bit of string manipulation. Let's make a start.
Get Image in Excel VBA
Set up a new Private Sub and call it GetImage. Add the following three variables to the new Sub:
Dim ImageFolder As String
Dim FilePath As String
Dim FullImagePath As String
Now scroll up to the very top of you coding window, and add the following variable:
Dim ImageName As String
The reason you're adding it to the top of the coding window is because it will then be in the General Declarations area, which will make it available to other Subs:
As soon as you press the enter key on your keyboard after typing the variable name and As String, you should see the line appear underneath it. Move your cursor back up to your variable name and you'll see the dropdown boxes change to General and Declarations
Variables set up in the General Declarations area will be accessible from anywhere. Much later, we'll need access to this ImageName variable. If we had set it up in our Private Sub, it could only be seen from that Sub.
To get the name of the image from the cell on the spreadsheet, add this line:
ImageName = ActiveCell.Value
To set the images folder, add this:
ImageFolder = "images\"
To go one folder up from the sheets folder, we'll create a function. This way, we can call the function into action whenever we need it. So create the following function in your code window:
Private Function NavigateFromWorkBookPath( ) As String
End Function
This is a Private Function called NavigateFromWorkBookPath, and should be outside of the GetImage Sub. The return type is As String.
Add these lines of code to your new function:
Dim WorkbookFolderPath As String
Dim SlashPos As Integer
Dim ImageFolderPath As String
WorkbookFolderPath = ThisWorkbook.Path
SlashPos = InStrRev(WorkbookFolderPath, "\")
ImageFolderPath = Left(WorkbookFolderPath, SlashPos)
NavigateFromWorkBookPath = ImageFolderPath
The first three lines just set up some variables. We then have this line:
WorkbookFolderPath = ThisWorkbook.Path
This gets the path of the workbook. In other words, the folder where you've saved your spreadsheet.
The next line is this:
SlashPos = InStrRev(WorkbookFolderPath, "\")
This uses the string method InStrRev. This is short for In String Reverse. This method starts from the end of a string of text and searches for a specific character. The character we want to search for is the "\" character. This gets us the position of the last backslash in our file path.
The sixth line is this:
ImageFolderPath = Left(WorkbookFolderPath, SlashPos)
We're now using the Left string method. Between the round brackets of Left, we first have the string we want to extract data from. After a comma we have the backslash position. The Left method will then get all the characters from the first position in our file path up to the final backslash.
So we're going from this:
C:\Users\Owner\Documents\vba\ projects\sheets\
to this:
C:\Users\Owner\Documents\vba\projects\
The final line of the function is the return value.
NavigateFromWorkBookPath = ImageFolderPath
So the string we want to return from the function is stored in the variable called ImageFolderPath.
We now need to call this function into action. Return to your GetImages Sub. Add this line:
FilePath = NavigateFromWorkBookPath( )
The NavigateFromWorkBookPath will return a string value. This value will be the file path of the folder one directory up from the workbook path.
To complete the full file path that we need for the LoadPicture method, add this line:
FullImagePath = FilePath & ImageFolder & ImageName
The variable called FilePath will hold something like:
C:\Users\Owner\Documents\vba\projects\
The ImageFolder variable will hold the name of the folder:
images\
The ImageName variable will hold the name of an image from the spreadsheet:
london_eye.jpg
The FullImagePath line joins all these parts together to give us something like:
C:\Users\Owner\Documents\vba\projects\ images\london_eye.jpg
It's now time to load the image into the image box. We could just do it like this:
Image1.Picture = LoadPicture(FullImagePath)
In between the round brackets of LoadPicture we have our new image path. This all goes to the right of an equal sign. To the left of the equal sign is this:
Image1.Picture
This sets the Picture property of Image1.
However, we can do a little error checking, just to make sure that an image at our location actually exists. Add the following If Statement to your code:
If Dir(FullImagePath) < > "" Then
Image1.Picture = LoadPicture(FullImagePath)
Image1.PictureSizeMode = 3
Else
MsgBox "Could not load image - no such file"
End If
The first line of the IF Statement is this:
If Dir(FullImagePath) < > "" Then
Dir is a method you can use to check if a directory (folder) exists. In between the round brackets of Dir we have our FullImagePath variable. If there's no such file in this directory then the Dir method will return a blank string. Which is what we're checking for.
If everything is OK then we can use LoadPicture. Notice the second line:
Image1.PictureSizeMode = 3
The PictureSizeMode size property allows you to fill the entire image box. We have ours set to a value of 3, which is really the value underneath a constant called fmPictureSizeModeZoom. The other constants you can use are:
fmPictureSizeModeStretch, which is a value of 1
fmPictureSizeModeClip, which is a value of 0
There is no 2, for some strange reason.
If there is no image at our specified location then we just have a message box:
MsgBox "Could not load image - no such file"
And that's it for the code to get the image. The code for your GetImage Sub and NavigateFromWorkBookPath function should look like this:
The only thing left to do is to add the Call to GetImage from your cmdLoad button:
Call GetImage
There's only two more lines to add to the cmdLoad button - the lines that switch on the Previous Photo and Next Photo buttons:
cmdBack.Enabled = True
cmdNext.Enabled = True
The whole of your cmdLoad button code should look like this:
Try it out again. Run your form and you should see an image appear when you click the Load Image Information button. You should also see the Previous Photo and Next Photo buttons are now activated.
If you get the "could not load images" error message then check the location of your images to see they are in the right place, one folder up from your sheets folder. If this still doesn't work, make sure Excel isn't working in Auto Save mode. If it is, it will be working on a copy of your excel file, which it places in a different location.
In the next lesson below, we'll write the code for the Next Photo and Previous Photo buttons.
Next Lesson: 10.9 Navigation >