Video instructions and help with filling out and completing When Form 2220 User

Instructions and Help about When Form 2220 User

And the youtubers this is Daniel strong with excel vba is fun today we're going to talk a little bit about some easier ways to do some data entry in this instance we have wanting to have some autocomplete options too and to make our own custom autocomplete and whenever you have a list and let's say you're typing things as you've typed them if you hit the letter T on the next line it autocompletes that for you it's very useful however I recommend whenever you're doing a lot of data entry that you have some lists already built up and that you can use user forms to make this extremely fast so let's go ahead and build we've already built our lists here a little bit um we're going to name this list I'm going to use a named range you can name it or you can make a custom or dynamic named range that will keep expanding so let's do that all IND if you don't know how to do this you might want to check out so my other videos on named ranges but we're going to name this um this one or something like that and we're going to say this list is based off of scuse me equals offset that comma one column down zero our scuse me one row down zero columns over and it's going to be based off of the count of that minus one to compensate for the title and in this case only one column wide it's not multiple columns in that offset thing here and then if you click the test you see that it's dynamic it grows so if I put another one in here and I hit alt IND for the name manager I click here it is expanded so there's my list it's called list one so when we're making our user form and hit alt f11 I'm going to insert a user form here and I'm going to have a few fields here I'm going to put a label another label and another label and we can of course name them and re caption them the first one would be the date and this is going to be a little bit crude a little bit slapped together for this for the sake of making a short video so we have our date we're going to have our list and our list is going to be what's called a combo box a combo box is simply a drop down menu this will be our list item for our data entry I'll double click on the corner and autofit that and label there and let's let's align all three of these here right-click and align their left sides so they're all aligned finally this one will be the what if we say here date list item in the user so that will be the user name and I don't know it depends on if you have users logging into your program or not but anyway we'll just have a simple text box that will stay the same then you have a little button that you can plop right there and we'll say submit and so that's a very basic form here and it doesn't do anything until you add some code but what we're going to do is when this user form initializes let's double click on the background and we'll go to user form initialize and it's a lot to take in I know if you've never used Excel VBA but trust me it's going to be worth your time so time saved right it's a stitch in time type of thing the userform initialized code is first of all going to say well let's go back and name these click here double click there we're going to call this TV date text box which is for the date this is a combo box so I'm going to call it C and B list item and this one I'm going to click and hit f4 and start typing TB user this button we're going to hit f4 and type BT in it's a button and it's a submit button so button submit is the name for that one now we can reference those whenever we're doing things so double click there again we're back on the userform initialize script here so what do we want to happen when that user form loads up well we want the me the user form dot TB date we want that to equal today's date which is a built in thing if you just type the word date and click away it'll auto capitalize that will make that text box be filled with today's date sounds pretty cool next thing we want to I when these are 4-minute relies it says we want to fill the combo box or the the drop-down menu and this is the data entry part that's awesome we want to fill that with with your list so how about this for each cell or for each whatever for each X for each blah whatever preach something rather in and then you just put those little square brackets and you give the name of the range Oh whoopsie the name of the range was list 1 remember when we made that a moment ago and you say next law our next cell or whatever is equivalent to that so for each one in that list reach cell in that list we are going to add an item to me dot the list C and B the combo box list item that we named dot add item that's a very simple and basic way to reach cell in that list add that item which was blah or which was cell or whatever we make these all identical for each cell make it that cell and then go to the next one in the list until