Excel VBA Forms Part 13 - Image Controls

welcome to part 13 of this tutorial on Excel VBA user forms this videos about image controls so it's all about displaying pictures on your forms while the forms are running we'll start by explaining how to draw an image control and then how to load a picture into it using the load picture method we'll explain a few of the common errors that are likely to occur when you do this for instance choosing the file that's not an image file or entering a file path for a file that doesn't exist and we'll explain how to write some error handling code to get around those problems well explain the three different picture size modes and show you how you can give your users the choice about which size mode they use when the forms are running and then towards the end of the video we'll create in my system that lets the user use a file picker dialog box to select image file rather than having to enter a file path manually it's quite a lot to do let's get started okay so here's the basic image controls example what this lets the user do is enter a path to an image file related to the film they're trying to add to our list I've got it set up so they can click on this little ellipsis button to browse for a picture and I've got a few sample images here related to some films if you haven't got these don't worry you can always just use the default windows sample pictures so you can always browse for those these will work as well and you can double click on an image to insert it into the image control we've got a few different tools going on here so we can chase the the picture size mode so we can switch between stretched and clipped and zoomed and we've got a little bit of validation so that if the user types in an invalid file path that that will prevent the user from continuing everything will look tasty I'm going to choose a related image the actual image itself doesn't get stored in the worksheet all that gets stored is the path to the image so when I click the add to this button with everything filled in all that we'll do is add in the path to that image file so that's our example let's get started creating it we're gonna need to do a fair bit of rearranging on the format make room for the image so let's start by reducing the size of our currency exchange frame then I'm gonna increase the size of the form so I can move that entire frame and all of its controls to another little section so let's just drop that just down below with the film genre section okay next thing to do is draw another frame into which we can draw our picture so I'm gonna increase the width of my form of it as well and let's draw a frame in that big section there that big blank section that we can use to store our picture I'm gonna make the frame quite talks we're gonna have a fair few different controls here eventually but I must have drawn the frame let's just make sure it's the same I just rest just about almost there we go and then what we're going to do is rename it and change it caption so the name of this frame is gonna be called film picture frame no pun intended and then we'll call that we'll just call it picture I'll give it a sorry a caption a picture I'll spell that properly as well then what we can also do is draw a couple of controls inside that frame so I've got the size quite right sorry to be so fussy about this but then we go next in series draw a text box that's going to hold the path through the file containing the image so let's draw a text box in there I won't bother giving this a caption I'm just going to draw it straight into the frame and then make it relatively wise let's fill the width of the frame I'm gonna change its name so it's called film picture path so obviously we've drawn frames and text boxes previously what we haven't drawn yet is an image control so if I click back into the form the image control is represented by this little tool here so I'm going to click on that tool and then click and drag somewhere inside the frame to draw that control not quite filling up the entire height of the frame I want to leave a little bit of room for some actual controls a little bit later on and then all we're going to do is rename this so that it's called film picture okay so that's all the controls drawn for the time-being what we can do now is get on on with how we set the picture that appears in the at the image control when a user types in path this textbox will use that before updated into the textbox to control what picture appears in the image control the main reason we're using the before applet event is to make it easier to validate this textbox later on so let's double click the check box to give us the default event handler for that one which is the change event which isn't quite what we want so we can use the drop down list at the top right hand corner and choose before updates and then we can just delete the other change event we shall be needing that now the code will go into add is actually quite straightforward I'm gonna add a single line of code that says film picture dot picture so the picture property is the property that controls what image gets displayed in the image control I can't just set this to be equal to the path of the image file it can't just be a string of text what I have to do instead is use the load picture method which will actually pass back a reference to the image itself but instead a load picture method I can pass in the path of the file that I want to load and the path of the file that I want to load is going to be stored in my film picture path dot aldi property so having done that I can simply go back to excel and then I can share the form on screen and as long as I type in a valid file path then my picture should appear when as soon as I try to navigate away from this control just to show you which images I've got available as I mentioned earlier on I've collected a bunch of film pictures and I've stored them in a folder called film picks and there is my C Drive if you don't have any don't worry you can always just use the default sample pictures that are installed along with Windows I'm gonna use one of my film picks so I need to copy the path first of all just to avoid having to type it in and I'm going to tag on the name of a specific file so if I type in a filename including its extension and then just well hit tab or click on to another part of the dialog box just navigate away from this control it should display that image in the image control if I were to enter and different image file instead so let's go for Titanic the same and its have to navigate away then again and we'll change the image that's displayed so that part's all working nice and straightforwardly what we also need to make sure we've done though is make sure that every users entered a file name that doesn't exist that the image file doesn't just do this if I tried to type in a file name it doesn't exist everything falls over so it's fairly obvious our message file not found if I hit the debug button is a fairly obvious line of code that's going to fail so you need to make sure we've handled that particular problem I think the simplest way to solve this problem is to add a basic bit of error handling code now we've created an entire video all about error handling in VBA as part of the main excel vba tutorial series so I'm not going to go into a huge amount of detail here please do refer to that video if you need a bit of background information on how error handling works but what I'm gonna do is I'm gonna write an on error statement immediately above the line that tries to load the picture and I'm gonna say that if anything goes wrong after this point go to an error handling label called I'm gonna pick prob has ensure the picture problem what I can then do is create that little label a little bit further down in the procedure so I'm going to type in pick prop down here and then I'm going to add a colon I've heard his name to create a label I want to make sure that I don't run into the error handling code should everything go correctly so I'm going to write an exit statement just below where I've successfully loaded the picture and then in the pic prop error handler what I'm going to do is say film picture dot picture equals load picture open parenthesis and then in a set of double quotes I'm going to enter nothing so I'm gonna pass in an empty string so what that will do is if there's a picture that's already loaded into the image control it will clear out any picture so it'll go blank again just to avoid potentially confusing the user showing an image that's not actually the latest of the file path they've entered I'm also going to make sure that I've changed the background color of the film picture path text box so as a film that picture path and lots of back color equals RGB pink which is the same color would be using for all of our other standard validation controls and I'm also going to make sure that I've cancelled this event so the user can move away from that text box until they've entered a valid passing as they cancel equals true just to make sure that when the user does enter a valid path later on that the background color of the text box goes back to being white I'm gonna write a bit of code to the after update event so the after update event now will only be triggered for this text box if the before update event has succeeded when we canceled the avender before update event it then prevents execution of the after update so we're all I'm gonna do in here is a film picture path dots back color equals RGB white and having added that simple little bit of code now we can go away and check that this works so back into Excel and if I showed the form on-screen I should be able to first of all this and a film path that does exist or a picture pad that does exist so I can say C colon backslash film pix backslash avatar dr. jpg and then if I tap away from that control that appears if I get that wrong this time all that happens is rather than a runtime error I get the picture text box so if the picture box empty again so goes back to a blank box and the background color goes pink for the text box if I reset that so that it's a valid file path of game and try to tap away from it and everything goes back to normal so that's that problem solved the next thing to talk about is how to control the size of the image you may be able to tell from this image that I've got loaded here that a lot of the pictures been cropped off so what want to be able to do is to change this so that I can see the entire image I'm gonna close down the form and then just do this manually first of all if I head back to the visual basic editor if I select the image control in the properties window there's a picture size mode property and the default property here is set to a clip so I'll chop off any bits of the image they can't display if I change this there's two other properties that stretch and they're zoom so stretch will make the image fill up the entire frame even if it means distorting the image in one one direction or the other if I choose zoom however which is the option going to choose now and then run the form again and this time I'm going to type in while typing the same path I should have just copied and basically they shouldn't I film pics and then a backslash and then say avatar jpg and then as soon as I step away from the control now I can see the entire image so you see it's the the dimensions of the box aren't quite proportional so I've got a little bit of them gray background there but at least I can see the entire image it might have to be nicer to allow the user to control how the images there are three different picture size modes as we saw in a little preview of such the video I could choose to display a little option buttons that like the user choose which picture seismo to use as I said doing that I'm going to close the form down and then we'll add in some extra controls to this frame now hopefully you've watched the video from earlier in the series where we've dealt with option buttons so we don't need to go into a huge amount of detail now about how they work let's start by drawing born on the forms below the picture frame or in fact below the picture image control rather than the picture frame and I'm gonna change this one's name so it's called film picture zoom and then I'll change the caption so that that simply says zoom change it with some what and then we can draw another option button and I'm gonna call this one film picture clip so film a picture clip and then I'll change its caption so that one's called clip and again change its width and then we'll add in one further option button which I'm gonna call film a picture stretch so we can call this one film picture stretch I just want to make sure also I only changed captions well so I'll call this one stretch I could make sure they're all the same sort of sorry aligned properly I do get slightly fussy about stuff like this and make sure there's no white space between them that looks reasonably well neatly laid out so one of the things I'm going to do is not actually necessary in this case because they're all part of a frame anyway so they're all part of the same option group but just to make absolutely certain of that what I'd like to do is assign a group name to these three option buttons so having selected them I'm gonna go to the group name property and I'm gonna call this one film picture size so that's the name of the group now just make sure they're all controlled together so that only one of these three options can be selected at once the next thing to do is out code so that when one of these option buttons is selected it changes the size mode of the image so this isn't particularly sophisticated all we're going to do is use the change event of each of these three option buttons to determine which size mode to apply so I start with the with a zoom option button if I double click on that one first you'll give me the click event of that option button I just want the change event for this one so I'm gonna use the drop down list at the top right to choose change and then get rid of the click event now because this is going to be done in three different procedures it makes sense write a separate subleasing for this that's what I'm gonna do here and as a private sub change picture size and then in here all we're going to do is use a simple if statement so I'm going to say if film zoom so film picture zoom back button film picture zoom not value so this is a boolean property so I could save film picture zoom dot value equals true but it's absolutely not a so to say the equals true part so I can just say if film pictures you've got value then all you hear is say film picture dot picture size mode equals and then choose one of the three options that make sense of course here to choose the zoom option and then I can have an else--if statement so it's essentially the same basic structure for each one of these so I can say film picture zoom top film but resume rather than film which is zoom I can say film picture clip body then I want to set its size mode to be equal to film picture size mode clip and then one last one I can just copy and paste that one more time this is the only only other available option so I could just have an else here rather than an else--if but just in case later when I add in some extra options as a film a picture stretch and then make sure that the size mode is indeed equal to size mode stretch and then and if so I can then simply call that procedure from the change event handler their film picture zoom so I can say call I don't need the call keyword you may remember from previous videos this is optional but I tend to put it in just to indicate that I am calling here I've written rather than just a matter that already exists in VBA so change picture size and then I can while I'm here choose the other two option buttons so I've done the zoom one I've got the stretch one here and that gives me the click event whereas I want the change event so I select that and delete the click event and then I just need to call the same procedure so I'm gonna copy and paste and that line of code and then one more time let's find the film picture clip option button and again just paste in the exact same call to the same procedure okay so having done all of that now if I switch back into excel I should be able to show the form on-screen and again I'll type in a quick file path I'll go for avatar again but jpg and then tab to move away from that control so we should see that it's set to zoom by default which is the size methought I chose earlier on but if I click on any of these are the two option buttons it will then change the size mode automatically what would also be quite nice would be one the form first loads for its have the zoom option selected you may have noticed before I clicked on the zoom option it wasn't selected none of these three options were selected so what we're going to do is just very quickly close the form down and then go back to the visual basic editor and then I'm going to go to the initialize event of the form so I'm going to double click the background of the form and then just down towards the bottom here where I've set up all the various default options already I'm gonna say film picture zoom dot value equals true I'm gonna sign a true value to that option button by default so one more time back into Excel this summer I'll launch the form it's already got the zoom mode selected in the introduction to this video I showed you that there was a nice little button we could click on to let the user select the file path that's slightly trickier to achieve than it really should be but let me give you the basics of how it works first long and closer form down and about the visual basic editor we need to start by changing a couple of properties of this textbox the really annoying thing about this is the properties I want to change aren't actually displayed in the properties window at least they're not for a text box bizarrely they're available for a combo box but show you what I mean by that ice lets the combo box we add it in in a previous video there are properties to do with the drop button style so you can see here there's a drop in the style they just widen this this properties window there's an arrow style and plane and an ellipsis and reduce we're gonna go with the ellipsis style for our text box but sadly I can't choose the drop button style for the text box that properties not available there's also a property related to a comma box box / to do with when the drop button is displayed so it says show drop button when and that's set to to always display it rather than one is activated or when it's focused or or Never so a text box should have the same property but it's not actually available in the properties window so the only way we can change these properties for a text box is in code so we can add a little bit more code to the initialize event of the form by double clicking on the forms background and then adding in a couple of extra lines of code to the initialize procedure now we only need two lines of code to make this work but the other irritating thing is that we don't get any help via the intellisense so what i'm gonna do is i'm going to say film picture path dot and the first property I want to change is what drop button style I use but you'll see if I say drop button style this doesn't appear in the intellisense annoyingly when I type in an equals sign I do get the list the enumerated list of constants so I can choose this specific style so this give me a bit of help but it appears that that drop button style isn't available for a text box this will actually work again same thing then for film picture path and dot show drop button when equals and again I get the enumerated list of constants all I mean that they indicate that this property does exist but it doesn't appear in the intellisense for a text box I'm going to choose show drop button when always so having chosen those two options or set those two properties when the form is initialized if I just go back to excel now and then show the form on screen I find that I've now got a nice little access that i can click on to do something useful of course it will actually do anything yet I've got to add the code to make this work so that's the next step the first thing we have to do is choose which event we attach this code to the city much easier choice than you might expect if I close down the form and then head back to the visual basic editor and then I'm going to double click on the text box to get into the code view we've seen the after and before update events from earlier on the events were going to choose here is called drop button click so really weirdly a text box has got this drop button click event even though it does everything in its power to hide from you the fact that you can add a drop down into a text box really bizarre anyway drop button click that's the event we're going to go for so what we need to do is add some code into this procedure so that when we click on the drop button it displays a dialog box allowing us to pick a file so we're going to choose a file dialog box to do this and again as part of the main excel vba tutorial series we created an entire video that explained how file dialogs worked so again we're not going to go into a huge amount of detail here just enough to give you the basics and if you need reference to some of the more complex advanced things there's an entire video about that in the other tutorial series let's start by saying dim FD as a file dialog I'm gonna set FD to be equal to application dot file dialog and open some parentheses and the type we're gonna go for here is a file picker there are a few properties of this dialog box I want to set so I'm gonna make sure that I've I'm only gonna live easy to set a single file rather than multiple ones I'm going to the FD dot allow multi select equals false I'm going to change the title of the dialog box so it says after you dot title equals choose image that's just a cosmetic change that will do nothing really another cosmetic change will be the button names I'm going to say FD dot button name equals select this image the what the main thing I would like to do is point the user to a specific folder to be so in the dialog box first launches it's already pointing to a specific path so I'm gonna say FD dot initial file name she's a slightly unusual name for the property but but they said she set the the default folder that is pointed to as well as a big default file so I say initial file name equals and C colon backslash film pix so that will avoid me having to type it in multiple times the last couple of basic things I need to do are to display their file dialogue on screen so I can say FG dot showed to make that work and then I want to pass in the path of the file that I've selected into the value property of this text box so once I've clicked the button on the file dialogue file picker I can say film picture path and dr. value equals then I need to refer to the selected item in the file dialog so I can say F d dot selected items this is this is actually an array of items this one base array so I'm gonna I need to refer to the first item that I've selected I've only allowed one single item to be selected anyway but if I have the allow multi slit there are multiple items I could select that's why I call selected items rather than item so the basics of this will now work there's a little bit of what to do to get it to to work perfectly but just to show you the basics if I go back to excel show the form on screen and then click the little ellipsis button here's my file dialog pointing to the film pics folder by default so I can click on the image that I want and then select this image and then I've got to of course make sure that if I tab away from that control it will update to display the image okay so that's the basics working but there's a few tweaks we need to make to this Toccoa to be working perfectly first of all an easy change to make it's slightly annoying that when I've selected an image from the dialog box and click to select this image it doesn't update automatically I have to tap away from the text box in order to make that update so the simplest way to make this work is if I just close the form down and then head back to the visual basic editor and go back to the code behind into the text box I mean I could write a line of code again that changes the picture property of the picture or the image control but the simplest thing by far - because I've already had to draw that coach to the before and after update events of the text box all I really need to do is trigger the update amount so I can make that work just by setting the focus to any other control on the form so perhaps a simple thing to do would be to say add add to list that's the name of the button for default button on the form Attalus don't set focus so if I do that that will trigger the update of that text box so again just creating a heading back to Excel showing the form on screen and then selecting a file clicks like this image and as soon as I do that this time the focus gets set to down to list button but that'll trigger the update of the image the next problem to solve is what happens if I display the form on screen but then either close it down or click cancel without having selected an image if I do either of those two things what happens is I get a runtime error and that happens because I'm trying to refer to a selected item when there are no selected items in the dialog box so I'm going to reset the procedure at that point with the reset button and then get back to the code behind my text box so need to add in a quick extra line of code that will check that something has been selected to make this work I've got to be able to detect which of the buttons I clicked on the file dialog box and in order to make that work I'm going to declare a variable which I'm going to call result as integer now the result of a dialog box isn't just what items you've selected the result is the actual button that I've clicked and I can retrieve that by setting the result to be equal to the show method of the dialog box so when the dialog box is finally closed down by clicking one of its buttons it returns a value to the result variable and the variable result will be either zero if I've clicked the cancel button or closed it down with the cross or it'll be minus one if I've clicked the action button the one that selects the file so a really simplistic thing to do here would be to say if the result was zero sorry if a result equals zero then simply exit the subroutine so don't do anything so that won't do anything to change the existing file path of the image so it'll just be as though nothing had happened at all so having done that if I go back to excel and launch the form and I'll select an image properly the first time around and then if I click on the ellipsis again and this time I choose cancel nothing happens if I share it and click the cross nothing happens if I run again inside a different image then it shows that image so that's that problem solved another potential problem I've got with my file picker dialog box is that currently there's no restriction as to what type of file I can select it just happens that at the moment I'm pointing to a folder that only contains JPEG files if I point it to a folder let's say the desktop for instance and picks a non image file let's go to the next sole excel file the next slice M file but you to select this image which clearly it didn't an image at this point the whole thing falls over I just click debug the reason this is falling over is because I'm trying to set the focus to the add to list buttom but I can't set the focus the add to list button because what's happened at this point is it's triggered the validation code in the film picture path before update event so it's canceling out of that procedure so that means it can trigger the after update event which means it can't escape from that text box or it can't set the focus to any other control so what's a simple thing to do here the simplest thing is to make sure that I can't select anything other than image files from this file picker dialog box so I'm going to stop the code from running with a reset button and then head back so they're carried behind the film picture drop a button and click procedure we can add a couple of lines of code then to set the filters of the dialog box when it appears so I'm going to give myself a couple of blank lines just below what I've set the basic properties of the dialog box and then first what I'm gonna say FDR filters dot clear so that will remove any existing filters from the dial boxer default one being the all files filter so that will now be gone what I can then do is say F G dot filters dot add and I've got two parameters to fill in here the description and extensions so the description is completely free text we can type in here whatever I like but I think it'd make sense to say something like image files the extensions must be valid file extensions so what I'm gonna do here is enter let's say you have three I think I'm gonna say asterisk dot BMP four bitmaps then a semicolon and then I'm gonna say asterisk dot gif or Jif I've read recently that it was meant to be pronounced jiff rather than gift which I found really odd as the G stands for graphics just hard cheaper anyway that's me so we'll go with with Jif or gif whatever you want to say then a semicolon and asterisk and dot and jpg that's not the full range of file types you can use in a and image control on a form just buddy buddy quickly show you from the MSDN reference page I can use bitmaps curve I think the cursors Jeff's Ike of icons JPEGs and WMF sup Windows Media File I think anyway those are the air those are the other valid options we're just sticking to these three so bitmaps gifs and JPEGs so having done that now if I switch back to the visual basic editor and in fact if I switch back to excel and show the form on-screen I should find now that one I launched the dialog box it's filtered for just bitmaps gifs and JPEGs so if I tried to put a list to another folder such as my desktop even if there are other files in there such there were two excel files and there was a text file those aren't displayed now because they're filtered for just these file types so that's the easiest way I think to control what file types the user can select the final and very basic thing that we have to do now is make sure that when we couldn't be add to list button whatever file path we've entered into the picture box gets added to the worksheet so that's close the form down and back to the visual basic editor again and we'll go back to the code but I need to list button and I want to right click on our data to list and then choose to view its definition just to get back to that procedure and then right down on the bottom it'll list now but we've added in all of the other units of information we're gonna say active cell dot offset zero comma nine dot value equals film picture path and dot and value simple as that so now that the whole thing is complete or let's go back to excel miss you could give it a quick test if we launch the form and we'll enter in some proper details I think this time so we'll enter in some proper numbers and a proper date and then we'll make sure we've selected a certificate and the run time and make sure we've specified whether we've seen it or not check a couple of boxes for the film genre and then let's select the picture for the film as well so click purchase button choose the appropriate image or inappropriate images that might be then finally click the Atlas button and we should find that everything now gets added in to the list just as we'd expect so there we go there's your quick introduction to image controls on forum so we found that on a bit more fun join us for the next one which is gonna be about multi page controls if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials meeting some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 24,792

Rating: 4.9565215 out of 5

Keywords: excel vba, vba, user forms, form, image, image control, picture, jpg, bmp, wise owl

Id: DmUZSDYiSDA

Length: 30min 39sec (1839 seconds)

Published: Thu Mar 31 2016