Video instructions and help with filling out and completing Fill Form 8815 Worksheet

Instructions and Help about Fill Form 8815 Worksheet

Hey guys it's Dan with excel vba is fun welcome back what I want to go over today is how you can populate a user form we're just going to create a few fields in a user form one two three fields two of which are textbox and one will just make a checkbox and what we're going to show you how to do is really cool we're going to trigger it by whenever we click on the row that the user form would pop up so you click on the row number now we've shown you how to do that with a right-click event in the past but somebody wanted to know how to do that when you click on the row so and then it'll bring up the user form which will already be populated with Sally green and false in this case and then you can let's say make a simple change and click Save and it will repopulate it back onto your data sheet whether or not this sheet is visible or not so we're going to review some of those things stick with us and we'll get to it first thing of course is we're going to start in alt f11 alt F 11 gives us to the visual basic sheet or editor we're going to click here and add a new user form it doesn't have to be too big I'm gonna make this really kind of crude and you can download this and play around with it yourself I'm not even going to put too awful many labels here I think this one was is parent that was a checkbox and then okay fine I guess I will put some labels here there's a label and there's a label this one is going to say color and it should say favorite color but I'm trying to rush here this is name and let's ctrl click and scooch these a little bit there alright so the first thing we're going to do let's give this a name in the names box if this doesn't show up hit F for TV name for text box name okay this is going to be TV color this will be see B as in check that's just preference CB is parents it's more meaningful when you have a lot of code and you need to refer to one of these controls because then you can do so with that without much thought put a button and how about save will dress that later all right so and when we run this user form of course nothing it doesn't do anything yet does nothing not we have a program for anything and I just collapsed that down a little bit here what I want to do is whenever this is the entire row is selected that's when I want these form to pop up and when it does pop up it should have whatever's in column one of my selection to be in the name one and so on so I'm gonna let's show you how to do that we're going to head back to the visual basic editor and again pardon the crude volume on this recording I'm away on business and I have a laptop here so I don't have my headset but anyway what we're going to do is we're going to go to the sheet that we're working with and we're going to go to a worksheet macro event and the default one is the one we actually want is a selection change so we want it to trigger whenever things are selected but I only want to trigger it really when the selection is the entire row so there's what there's a few different ways you could do that we're going explore one so how do you trigger a macro to only consider certain certain cells and other ones it's not really going to trigger well we use if not intersect and you can see more information on this using my other videos just type in if not intersect but basically it's going to take into account the target is the thing that we select when it which triggers a selection change the target is what we select so if the intersection of the target and what I want it to be is nothing meaning not nothing than is something so basically if what I've selected and what I want to be my criteria have intersected then I want you to do this otherwise who cares is what it's saying here so the area that we want to do is we'll just say a two through a I don't know ten thousand or something a two through ah Z one thousand ten thousand so if that intersection is nothing then or is not nothing so then we trap it so let's go ahead and see if we can trap it I'm gonna hit stop and I'm going to click on this all right here all right so we have a type mismatch so I should have said range of a two through Z ten thousand now let's hit f5 so the target is in cell a3 and is that within the range a2 through Z gazillion whatever yes now what if I select the entire row it's considering that to be within a two through Z whatever so that's good so that was the first part now we're going to do one more if statement for fun we're going to say if target dot let's see what was it going to say here if target dot address is equal to the target which is also called selection in normal cases if the targets address is the same as the target dot entire row dot address then we have a match yes full row selected made a little comment there and if so now if the targets address which is three through three is