Excel VBA Basics #5 - Toolbars, Messageboxes, Object Browser, Autocomplete and Code Windows

hey welcome back youtubers this is Daniel strong and this is excel vba is fun thank you so much for joining me again today we're going to go through a wide array of different topics several several of which have to do with clicking different em different buttons appear on the toolbar so right off the bat you may see that I have a few extra things that you normally see up here to access them you can go to view and toolbars I typically instead I'll just right click up here anywhere in the gray area and I typically will have the edit toolbar so I right click down here and I click Edit you see it's checked the standard toolbar I always have as well that's where I get the play button or as you know like the play button f5 or resetting breaking in the middle of macro and save and all these different buttons that are essential I want to talk to you a little bit about what these things do here I don't believe we've gone over those so let's talk about them list properties we're going to go over that in a minute you can click it anywhere if I hit click it right now or click ctrl J it's going to immediately open up a drop-down menu that's got hundreds of commands and different things that you may be looking for that you might want to fish around if you don't know moptop your hit and clicking away will remove that again it's ctrl J so you'll find all kinds of things active sale active chart active sheet dot and if I hit a period on some of these they'll continue having more dropdowns for more properties within methods let me tell you some of the main ones that I use is right here and then you can click tab to indent things or shift tab to on or to out dent and what I mean by that is let's say that you had it's one thing to click right here and hit tab click here and hit tab click here and hit tab I'm going to ctrl Z to undo ctrl Z ctrl Z but what if you had like hundreds of lines since you wanted to tab them all or indent them in you can actually select one of many ways you can select like this and I'm going to hit the tab button instead of clicking this you can click indent here or outdent like this I can hit tab and shift tab shift tab shift tab like this and that's one way to indent I'm going to talk to you about commenting a block of text or one line and uncommon ting if you've ever used batch files you've seen people write remarks that they put rem and then they type the remarks and all that stuff is not run as it's not considered code it's considered a remark well we can do that with Excel as well we can actually just start a line of text with a comma and type whatever we want and it won't give me any errors you notice it goes green immediately it won't give me any errors I'm going to take that out because that is not a valid note let's say that these two lines here I did not want to perform if I click on both of these and highlight both of these rows here I can actually click this little green comment block and what do you know it inserted an apostrophe right here in the past tree right here and now it will override it we'll ignore these two lines so they have been commented out and you can do that several times and you can uncommon them the only time they become readable code is the final uncommenting when there's no apostrophes here and then it will consider these lines of code not skip over them so you can do that if you want to write notes to yourself I recommend doing that I would say this area of code blah blah blah you know is the date is next week that's what date plus 7 would be next week and then you might put a comment here of whatever this next line is so that's how the comments work this stop button here is called toggle breakpoint you can hit f9 what I do typically do if I want to if I want the code to stop at a certain point you like you've seen in a previous video you can type the word stop and it will run all through the code but it'll stop at your stop marker now I'm going to show you another way to do that it's a little easier and it won't stay there is easy you can in this a gray area you can let's say I wanted to stop it here at this line I click right here and that's how this the same thing is toggling the breakpoint so if I hit f5 to run my code yet actually stopped right there so if I you know if I hit f5 again it's going to run to the next value of X which is 3 if I hit f5 again it'll be for now but it stops instead of running really really quickly through the whole code it stops right there so that's toggling a breakpoint I also want to let you know if you ever want to skip over a line just once in a great while you're debugging your hitting f8 you can actually take this yellow point marker and skip over something and just go right or right where you want to go let's say you're at the end of a code and you're like well I want to run this line again and see what happens you can actually drag the yellow little arrow to where you want to go so that's that's it for that area a few other things there's an object browser I want to let you know about this is where you can search and look up different terms and it's also you can get there by hitting f2 let's do that let's hit f2 instead okay now I'm in the object browser let's say I wanted to know about the print preview quality of something I have in print preview and I hit enter and it brings up all the instances of print preview in anything in the Excel options so like for example sometimes I use this sheets and then you name the sheet like for example we did sheets my report and then we close it up with quotes and parentheses then you could do dot and it's showing you here and you can do dot print preview and that would immediately take you to a print preview in fact I can show you that real quick to get back to our code you can click view and hit F and go to code again that's F 2 to get to the object browser f7 will get you right back ok we could do that really quickly we can actually not only do it this well that you may be in a future video on backtracking here because the audio was lost but and in this other one we see that you can make it make a report or a sheet visible or invisible right here so actually I think I'll wait on the print preview property but you could do something like that this workbook that sheets my report that is the current sheet down here called my report and you could do dot print preview right here and that would actually bring up a print preview allowing the user to just immediately hit print without you know they'd be able to see that so that is your object browser we've gone over the immediate window some properties window that's down here if that's not up you can click view properties window or you can simply hit F for you here you can view lots of properties there's not so many in a module where we're containing our code right now but let's say in the sheet one which is called my report there you can name it the the actual name not the nickname here the object name you there's display page breaks is by default assets of false if you change that to true I guess it would display page breaks you've got enable auto filter enable calculation and that's just for this sheet so you can actually do a lot of things like that enable selection by default there's no restrictions however you could restrict it to only unlock cells by clicking here and that would mean that even if they hit the left and right arrow keys it would only go to the next unprotected cell or there's no selection where you can't click on anything so you can do a lot customizing these these sheets and then you got the visible problem quality here which you can change from the code as well so you've got the visible equals excel sheet visible by default they're all visible those sheet hidden and then there's sheet very hidden the only way you can unhide one of those sheets is using a visual basic code the user will not even be - right click and go to unhide a will not be available so there's lots of properties there's this workbook properties you can explore here and so you can mess around with those if you ever have a user forum and we have not really messed with that much I clicked here and went to user forum there's all kinds of properties in here especially when you start adding buttons and you've got the name of the button and all these different qualities the color all these different things so text boxes and all these things so they all have properties that can be found here in the properties window is that's going to be super duper important when we get to those for the time being I believe we're done I don't think there's anything else I had to go over let me see ok yes I do remember um the last thing I'm going to go ahead and remove this user form and no I do not want to say we're exported I wanted to show you about let me go back to module 1 we're going to make a quick macro I had somebody asking me about how to do a message box what's up with the message box so let's do that really quick we'll just call this and make a new macro sub we'll call it message keep it's a bomb hit enter let's just do something simple whenever the user runs this macro what we're going to do have a message box pops up that says I'll do the traditional hello world let's do that to do a message box you simply type em as G vo X and you can hit space and you'll see the ticker here is the first thing you need is a prompt so what are you wanting to the message to say let's start with text we're going to start with quotes hello world ahead of myself hello world exclamation mark end quote now if I hit comma because I want more to it I'm gonna hit comma here's your buttons what buttons you want it to say have a yes and a no and a cancel or do you just want yes or no do you want just okay in this case we just want VB okay only we just want them to be able to hit okay not yes or no and have things happen from there we'll go over those options later I'm going to hit common to get beyond my buttons here what about the title let's give it a title quotes how about howdy okay now that's all I don't want to have to put a help file and program all that and do the context I'm not going to do any of that we're just gonna have a simple macro that runs a Mac a message box that says hello world and the user will only be able to click on the ok button and it'll say howdy in the title let's try it so I'm going to hit alt f8 that is your macro toolbar here and I'm going to double click on the one that says message let's run it and oh a message box pops up says hello world and says how do you the title and sure enough there's only an OK button real quick I'll show you what would have happened if I would have typed VB yes no VBS no only I guess it is oh my goodness let's go here and hit comma and see what our options are VB will VBS no maybe I did a typo I was waiting for the capitalize that let's try that it won't do us any good but let's some alt f8 we'll click on the one called message and it says hello world but now there's a yes and a no clicker and neither of them do anything but continue the macro and let it finish its course so thank you for watching and God bless and I hope you join us next time for Excel VBA is fun thank you

Channel: ExcelVbaIsFun

Views: 179,933

Rating: 4.9053497 out of 5

Keywords: object browser, code window, vba is fun, excel vba is fun, autocomplete, excel vba tutorial, macro, VBA, Visual Basic, reports, reporting, debug, Computer, excel vba, programming, ms excel, ms excel vba, excel visual basic, thisworkbook, object, vba basics, vba tutorial, visual basic tutorial, range, excel, excel tutorial, excel basics, Toolbar, Messagebox, msgbox, vba code, easy vba, visual basic, vba msgbox yes no, excel vba advanced, excelvbaisfun

Id: NqDCC7bonuE

Length: 12min 44sec (764 seconds)

Published: Thu Feb 21 2013