10.10 The Add New Photo Button

Ongoing tutorial - First part is here: Part One

What we'll do for the Add New Picture button is to display an Open File dialogue box so that you can select a new image. The new image will then be loaded into the ImageBox control on the form. We'll also put the image name into the tbImageName text box.

To make a start, select your Add New Photo tab at the top of your form. Now double click your cmdAddNew button to open up a code stub.

Before we look at the Open File dialogue box, add a new variable to the General Declarations area. Add the following:

Dim CopyImage As String

Your General Declarations area should now look like this:

Two variables set up in the General Declarations area

The reason we need this variable here is because we're going to copy the selected image over to the images folder. The image folder, remember, is where we store all the pictures referred to on the spreadsheet. The image you want to add might be in a different location. So copying your chosen image over to the images folder ensures that all your pictures are in one place.

The Open File Dialogue Box

When displaying an Open File dialogue box, you have the option of setting an initial directory. This is the folder you want the dialogue box to be in when appears. What we want is the images folder to display. To make that happen, we can call our NavigateFromWorkBookPath function again. We can then add the images folder to it:

Dim FilePath As String

FilePath = NavigateFromWorkBookPath( )
FilePath = FilePath & "images"

Add the three lines of code to your own cmdAddNew button. The & "images" is the part that adds the folder called images to the file path we got from the function.

If you managed to add an Open File Dialogue Box Control earlier then follow along with the steps below. If not, then click here to go to your lesson: 64 BIT TUTORIAL

To display an Open File dialogue box the code is fairly simple. It's just:

CD1.ShowOpen

(The CD1 above is the name of the CommonDialog control you added when you were designing the form.)

There are quite a lot of extra properties you can set, however. The Initial Directory is one of these properties. You use it like this:

CD1.InitDir = FilePath

Before the equal sign, you have the name of your CommonDialog control followed by a dot. After the dot, you type InitDir, which is short for Initial Directory. After the equal sign, you can either hard-code a file path surrounded by double quotes, or you can have the name of a variable. We have our FilePath variable, which contains the path to our images folder.

You can also add a title to the top of the dialogue box. This is done with the DialogTitle property:

CD1.DialogTitle = "Get Image File Name"

Whatever you want as the title goes between double quotes, after the equal sign.

One thing you will want to set is the type of files that the dialogue box can display. This is done with the Filter property. Examine the following:

CD1.Filter = "JPEG Images|*.jpg|GIF Images|*.gif|BITMAPS|*.bmp"

Before the equal sign, it's pretty straightforward: just type Filter after the name of your CommonDialog control. After the equal sign, we have this:

"JPEG Images|*.jpg|GIF Images|*.gif|BITMAPS|*.bmp"

Here, we want to display three different image types: JPG, GIF, and BMP files. Let's look at how the JPG image type is laid out:

JPEG Images|*.jpg

The part before the pipe character (the | symbol) is what appears in the dropdown list to the right of File name:

File Types in the Open File Dialogue Box

To the right of the pipe character you type an asterisk (*). This means all file names. You then need a dot followed by the type of file you want to display, which is jpg in this case.

To add more file types to the dropdown list, you repeat the process:

|GIF Images|*.gif

Notice that there is now another pipe character, at the very start. This is used to separate each file type.

If we wanted to specify files of any type and any name, we'd do this:

"JPEG Images|*.jpg|GIF Images|*.gif|BITMAPS|*.bmp|All Files|*.*"

Notice the symbols used for All Files - two asterisks separated by a dot.

One final point to make is that all of your filters need to go between two sets of double quotes.

The Open File dialogue box, however, doesn't actually open a file: it just gets you a file name. This is done with the Filename property:

fName = CD1.Filename

Because you're trying to read a value, you need the Filename property to the right of an equal sign. To the left of the equal sign is the name of the variable that's going to store the file name.

With all that in mind, add the following code to your button:

Dim fName As String
fName = CD1.Filename

Your coding window should now look like this (we've added some comments):

VBA code to display the Open File dialogue box

32 but users can now move on by clicking here: Get the Image Name.

64 Bit Open File Dialogue Box

To display an Open File Dialogue Box if you didn't add the control, the code is quite different. It's still fairly straightforward, though. Add the following to your coding window:

Dim fName As String

With Application.FileDialog(msoFileDialogOpen)

.InitialFileName = FilePath

.Filters.Clear
.Filters.Add "JPEGS", "*.jpg; *.jpeg"
.Filters.Add "GIF", "*.GIF"
.Filters.Add "Bitmaps", "*.bmp"

.AllowMultiSelect = False

If .Show = True Then

fName = .SelectedItems(1)

Else

MsgBox "Operation Cancelled"
Exit Sub

End If

End With

We're using a With Statement, here. But notice what comes after With:

Application.FileDialog(msoFileDialogOpen)

After the Application object, you can add a FileDialog method. In between round brackets, you need to add one of four available built-in constants. The four constants are these:

msoFileDialogFilePicker.

msoFileDialogFolderPicker.

msoFileDialogOpen.

msoFileDialogSaveAs.

The one we want is msoFileDialogOpen.

In between With and End With we first have this:

.InitialFileName = FilePath

The InitialFileName is like the InitDir above: it sets the folder that you initially want to start from.

Next, we set up some filters:

.Filters.Clear
.Filters.Add "JPEGS", "*.jpg; *.jpeg"
.Filters.Add "GIF", "*.GIF"
.Filters.Add "Bitmaps", "*.bmp"

This sets the type of files we want to display in the dialogue box. The first instruction clears the dropdown box, otherwise you'd end up with lots of other file types that you don't need. Next, we add the filters, one per line. The asterisk means "all files of this type".

The With Statement also sets AllowMultiSelect to False. The final thing it does is to place the selected image into the fName variable. But we need to do this in an If Statement:

If .Show = True Then

fName = .SelectedItems(1)

Else

MsgBox "Operation Cancelled"
Exit Sub

End If

To place the select image into the fName variable you use SelectedItems. The 1 in round brackets means the first selected image. If you had set AllowMultiSelect to True then you could have used a for loop to loop through each file that the user selected.

The Show part is the one that actually displays the dialogue box. If it has a value of True then a file was selected and the user clicked Open. If it has a value of False then the Cancel button was clicked instead, and we can exit the Sub.

Your coding window should look like this, though:

Open File Dialog code for 64 Bit systems

Get the Image Name

Next, we can get the image name. We need this for the text box. Here's the code to add:

Dim SlashPos As Integer

If fName < > "" Then

SlashPos = InStrRev(fName, "\")
ImageName = Mid(fName, SlashPos + 1)

End If

If you click the Cancel button on the Open File dialogue box then the file name will be blank. The If Statement checks for that. If it's not blank then we extract the image name with these two lines:

SlashPos = InStrRev(fName, "\")
ImageName = Mid(fName, SlashPos + 1)

First, we get the position of the last backslash (\). This is then used in the Mid string method. Between the round brackets of Mid we have the string we're trying to chop, which is in the fName variable. After a comma, we then need a starting point to start chopping text. The starting point is the position of the backslash plus 1. Because we haven't specified an end position, Mid will chop to the last character in the fName string. This will get us the name of the image, which we stored in the ImageName variable.

The only other thing we need to do is to load the image into the ImageBox on the form. Here's the code for you to add:

If Dir(fName) < > "" Then

Image2.Picture = LoadPicture(fName)
Image2.PictureSizeMode = 3

CopyImage = fName

cmdSave.Enabled = True
tbImageName.Text = ImageName

Else

MsgBox "Could not load image - no such file"

End If

This is more or less the same as we did before, for the Load Image Information button. We're doing two things differently: one, we switch on the Save button by setting the Enabled property to True; two, we place the image name into the tbImageName text box.

There is one line in the code above that may seem a little odd. This one:

CopyImage = fName

What this line does is to store a copy of the file name in the CopyImage variable. This is the variable you set up in the General Declarations area. We'll need this file name when we copy the picture to the images folder. This is done when the Save New Details button is clicked. If we made CopyImage local to the cmdAddNew button then the cmdSave button wouldn't be able to see it.

Try it out. Run your form and click the Add New Image button. Select any image from your computer. When you click the Open button on the dialogue box you should find that the image appears on your form, along with the image name in the text box.

In the next lesson below, we'll write the code that saves the new image details to the spreadsheet.

< Navigation

Next Lesson: 10.11 The Save Button >

<< Excel VBA Course Menu

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