Creating Forms Using Excel

Video Transcript

"Hi. My name is Dave Andrews. Today I'm going to show you how to create forms using Excel. Now we're going to create a form for this sheet. To do so, hold down the Alt key and press F11, which brings up the visual basic code editor. What we're going to do is we're going to click on Insert, and we're going to say User Form. Now here's our form interface. We're going to drop a couple of controls onto this form, which is what these items are called, controls. We're going to start by placing a text box, which is this A and B interface here. Just going to drop these at the top, and a couple of command buttons. There's command button one and there's command button two. Let's go and resize our forms so that it looks good, all you see is the command buttons and the text box. We're going to name command button one Okay. Click on it and by caption let's just type Okay. Click on command button number two and next to caption, let's type Cancel. Now double click on Cancel and here's where we're going to enter our programing code. Now, let's go back to the form and double click on Okay. You can see the code that I put into here. It's basically three simple lines. The very first one will declare variable called WS, that's going to point to a worksheet. The very next line sets that WS variable equal to the active workbook in Excel, the sheets, one named Sheet One. And if I open up Excel, at the very bottom, at the very bottom in Excel, you can see the sheets. We have Sheet One, Sheet Two and Sheet Three. This code is going to select Sheet One. So let's go back to that code. Now, with WS pointing to that Sheet One, I basically told it to set the value of Cell One One, which is going to correspond to Cell A One, equal to the value of text box One dot Text. Now Text Box One is this text box that we created up here in our form. If I click on it, next to Name, you can see Text Box One. If this had been named My Text, then we would've done My Text dot Text. That's actually Text Box One. Now our code has been created, let's go back to Excel by clicking up here at the very top where it says View Microsoft Excel. And we're back in Excel."