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.

<< Excel VBA Course Menu

Lots more free online courses here on our main Home and Learn site

© All course material copyright Ken Carney