Excel Worksheet Events #2 Macro when you change a cells value, LEFT and UCASE functions

hey welcome back youtubers is Daniel strong with excel vba is fun this is lesson 2 in our worksheet events series we're going to talk about the worksheet change and not to be confused with the selection change event the worksheet change event is a little different instead of me clicking somewhere the worksheet change event only happens when you change oneself from one value to another example if I had a number in here and I deleted it or if I had nothing in here and I typed something in and hit tab or enter or clicked away as soon as I left that cell then that cell had officially changed so it may run so our objectives today we're gonna run a macro pointing at the total of quantity times price over here in the totals for the selected row when the price is typed in so we're gonna do that by doing one on another one those if not intersect is nothing codes and we'll go over that really quickly so we will make a macro put my name in there will say quantity is 1 price is 3 total is gonna be well that's yet to be seen alright do control shift dollar sign to make the price become fact we'll do that to column D as well control shift dollar sign all right now we're ready to get started alt Fe 11 for our first objective no I'm gonna I'm in the same worksheet here excuse me I need to go to the sheet 6 which is the change worksheet okay click on worksheet we do not want selection change I'm just gonna click on the one I do want which is the change event and now you see it's brought that up let's delete this other one well what the worksheet change event okay here we go we're gonna start by saying if not and if you want to know more about this if not intersect business please watch the previous video if not the intersection of the target cell comma and we're going to say the range of let's just do looks like we're neither the price so we're gonna need c6 through let's just do c6 through C 1000 and all that covered wanted in parentheses for the intersection is nothing and then and we'll run our macro from there and yes okay so let's see what happens so if if we are making a change on any a thing of c6 through C 1000 it will run this macro currently we don't have anything in there let's say if that's true let's see we need to get the totals so we're gonna say we're gonna save the cells this is you're gonna use target donna row we need to know the row that we're in currently target row comma and we want total so one two three four column four equals and we need to get the quantity times the price so we're going to say equals cells we need to row again comma column two times sells target that row comma 3d all right so what we're saying is that the fourth column of that row which is right here is equal to the second column of that row times the third column of that row so this should work let's go ahead and do a change here now as I hit delete on the price it'll probably run the macro and put zero in there but you notice it's not a formula it actually has the value zero there pretty cool I'm going to hit 450 and hit enter a 1 times 450 now you notice if I do that here nothing happens okay it's only when I write it you see 6 through C 1000 so we'll just say 120 0.33 and that's our total this is hard-coded this is again this is not a formula says equals sum or equals this times this whereas if somebody types over that accidentally or deletes it or something as long as I select this and hit enter it's gonna rewrite it that's the power of Visual Basic yeah you can't write over the formulas and screw things up once you've hard-coded it all right objective number two really quickly same as number one except the name is start with a P or in him that's exciting we're gonna use the left function in Visual Basic currently that starts with a D so let's go back called F 11 we're gonna analyze this a little bit so before we even allow that we have another if if left and what's the cell its cells target dot row comma 1 that would be the row we're on two comma column 1 G if left of that comma well just the first letter right not this not the first two letters so if the left of that the first letter equals P or and I'm gonna copy and paste that and just put in copy paste or if that is M then run this beautiful code and if okay now we're gonna run into a snag you're in oven and I'm gonna show you what well we can do about it okay let's say this is Margaret okay and I'm going to click here and put $4 J that worked what if we had my name in there I'll put $3 let's watch this cell as I hit tab Oh did not work okay now was let's experiment with something else I want you to see this Margaret with a lowercase M $5 oh you notice it didn't like that let's review what we actually told Excel to analyze we said if the leftmost letter or number of this cell is capital P or capital M then run the code okay so what do we do to get over this we want to analyze it no matter what they type a capital or lower case so you can use you case if the uppercase version of whatever this letter is is B or if the you case if the uppercase version of whatever this is is capital M then run it let's run it again I'm gonna hit one you notice it accepted Margaret now because it just capitalized it in its analysis it pretended like this was a capital M by using you case okay so we've completed our objectives one and two I think that we'll wrap that up thanks for watching and next time we're going to talk about the double click event

Channel: ExcelVbaIsFun

Views: 63,115

Rating: 4.9180326 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, worksheet change, worksheet event, events, change cells

Id: TpP9_KIpbdI

Length: 8min 24sec (504 seconds)

Published: Wed Feb 27 2013