Excel VBA ActiveX Controls #1 Worksheet Button and Textbox

welcome back youtubers Daniel strong with excel vba is fun and this lesson we're finally going to get started with these wonderful things called form controls ActiveX controls what are they they've their buttons combo boxes checkboxes option buttons text fields pictures and you can click on and make macros run toggle buttons text boxes scroll bars all kinds of good stuff so we're going to get started immediately today I think we'll just take a text box and a button and we will use those two in our lesson so what's a text box well how is it different from a cell cells contain text well a text box is hard to describe I'm going to go ahead I'm going to go ahead and click this insert button and we might as well go into design mode notice I'm on the Developer tab I'm going to insert I like to typically use these ActiveX controls because you can directly affect what happens when they're clicked rather than having to design a macro and then just run that single solitary macro off of the form controls so and note all that the that all these buttons here are somewhat volatile if you run a report sometimes they'll they'll locate or resize themselves on the page so that's why user forms are much more recommended we will go ahead and go over worksheet controls anyways so let's go ahead and place a textbox right about here you can size it any way you like let's size it about like that okay so there's our first one it by default it's called textbox one if I created another one to be called textbox to you can go into the name field well rename it we'll call this one TB for textbox and then a capital or let's call this person person one enter okay so now a TB person one is what this is called now we're going to go to insert and in the ActiveX controls will insert a command button I want that button oh it's a little big I'll resize that to about there and it looks like the font is jacked up quite a bit I'm going to right click and go to properties you'll see the properties menu pops up so the caption is what you may be seeing right here not the name but the caption so for example ooh why is that font so big let's change the font right here and we'll change that to let's say size 8 wonder if that'll that'll do it maybe we need to make it even smaller okay hello like I said some of these are pretty volatile so here's our button will we'll change that to press here all right you'll notice when you double-click on either of these for example come in we'll call this command button instead of command button 1 we'll call it put BTN button press here why not okay so if you click on them when you're in design mode it'll just allow you to edit them in the properties window or resize them we take design mode off and you have an actual why is it making those so large oh I guess it's because I zoomed in I need to make these little bigger yeah it's because I zoomed in so much that's okay if the zoom in I hold control and I use my scroll wheel in and out okay sounds ooh me okay so this is actually the right size with TV person we'll call this button press here now let's get to the nitty-gritty double-click on the press here button notice it says private sub button press here underscore click so that tells me we're off the bat there's a lot of stuff you could do you could make somebody double click on the button before it runs the macro but by default that just things you're going to click the button so there's a sneak peek in what I'm about to do what we're doing here let's say that somebody clicks on one of the names in our dudes list let's say they click on Dan that's a good name here so double click in here again how bout and you can say me dot and that does anything any button or macro or whatever that's associated with me me is in this case is the workbook this workbook so me dot TB TB person one that's our text box called person one dot value equals how about whatever the selection is so as soon as I click this button it's going to run this one-liner that says well you know whatever the selection is that's what I want the value of the text box called TB person let's try it and take design mode off so now you have a legitimate button let's click on this Dan Joe how about Sally oh cool go back to design mode just for kicks let's say this equals the word biscotti ampersand and whatever the selection is so let's try that whoops design mode off press here press here press here Scotty Joe let's open that up a little bit oops miss Scotty biscotti Joe biscotti Dan and if we want to put a space there we can obviously put a space marker there biscotti space and Joe or selection whatever selection is biscotti space Sally biscotti space chess Bob so anyway that's our first lesson and just note that whenever you are in design mode when you click here or here you can access the properties by right-clicking and going to properties and there's a lot of properties in your so check those out mess around with them thanks for watching

Channel: ExcelVbaIsFun

Views: 181,948

Rating: 4.2141452 out of 5

Keywords: Microsoft Excel (Software), macro, VBA, Visual Basic, reports, reporting, debug, Computer, excel vba, programming, vba excel, ms excel, ms excel vba, excel visual basic, microsoft visual basic, thisworkbook, object, vba basics, vba tutorial, visual basic tutorial, range, excel, excel tutorial, excel basics, easy excel, easy vba, easy visual basic, events, activex, control, button, textbox, form

Id: EZZ8e56Olo4

Length: 7min 3sec (423 seconds)

Published: Sat Mar 09 2013