Two questions today with a similar solution using a VBA Event Handler.
Question 1: Cells O19 and Q19 are mutually exclusive. If someone selects from the validation dropdown in one cell, the other cell should be cleared out.
Question 2: In a certain range where you have to enter large numbers, can Excel add three zeroes to any number you type.
Both of these are solved using the same event handler macro.
Concepts in this video include:
Changing Macro Security to Allow Macros ( 0:30 min )
Change file extension to Allow Macros ( 0:42 min )
Finding the worksheet code pane ( 1:06 min )
Starting a Worksheet_Change macro ( 1:20 min )
Using an IF Statement in VBA to check what cell just changed ( 1:50 min )
Why Target.Address requires dollar signs ( 2:02 min )
Why Event Handlers might cause an endless loop and crash Excel ( 2:52 min )
Using Application.EnableEvents to Prevent an endless loop ( 3:32 min )
Testing the solution to the first question ( 4:01 min )
Question 2: Adding three zeroes to a number ( 4:22 min )
Using INTERSECT method in VBA to see if recently changed cell is in a range ( 5:04 min )
Testing Solution 2 ( 5:40 min )
Preventing the Event Handler from Running if multiple cells are cleared ( 5:50 min )
Indenting Many lines of code at once ( 6:15 min )
Question 1: Cells O19 and Q19 are mutually exclusive. If someone selects from the validation dropdown in one cell, the other cell should be cleared out.
Question 2: In a certain range where you have to enter large numbers, can Excel add three zeroes to any number you type.
Both of these are solved using the same event handler macro.
Concepts in this video include:
Changing Macro Security to Allow Macros ( 0:30 min )
Change file extension to Allow Macros ( 0:42 min )
Finding the worksheet code pane ( 1:06 min )
Starting a Worksheet_Change macro ( 1:20 min )
Using an IF Statement in VBA to check what cell just changed ( 1:50 min )
Why Target.Address requires dollar signs ( 2:02 min )
Why Event Handlers might cause an endless loop and crash Excel ( 2:52 min )
Using Application.EnableEvents to Prevent an endless loop ( 3:32 min )
Testing the solution to the first question ( 4:01 min )
Question 2: Adding three zeroes to a number ( 4:22 min )
Using INTERSECT method in VBA to see if recently changed cell is in a range ( 5:04 min )
Testing Solution 2 ( 5:40 min )
Preventing the Event Handler from Running if multiple cells are cleared ( 5:50 min )
Indenting Many lines of code at once ( 6:15 min )
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1850.
One cell, or the other.
We'll use the worksheet change macro to clear the other cell.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's questions sent in by X Caliber.
He has two cells, O19 and Q19.
There's a data validation in both, so you can choose from a list and when we choose from one, he wants to clear out the other and sure enough this is going to be done with VBA.
If you've never used to VBA before, we want to check two things.
First, make sure that you can use macros.
So, do [ alt + T ], followed by M followed by S, that would get you to macro settings.
By default, it's going to be at the first one, to save a little macros without notification.
You want to go to the second one, secondly make sure that your file is not saved as xlsx.
This is very common, it's the default.
This is the only file type, that is a hampered.
You cannot have macros in that xlsx.
You currently have an xlsx mixer, do file, save as and change the save as type to xlsm, click [ save ].
All right! Now, we gonna switch over to VBA.
Notice, that we are on sheet1 down here.
[ Alt F11 ] If you just have a big gray screen, make sure go to view, project Explorer or [ control R M ].
Now, we look for our workbook and right click on the sheet name and say view code.
All right! Now, we have two things here.
We have a drop down on the left hand side.
Only one option there, choose worksheet and they automatically choose selection, change this macro would get run every time we select a new cell.
That's not what we want to have, happen here.
So, we go in and change the right drop down to change.
This is the macro that will run every time, we change the cell and very nicely, the macro gives us a variable called target, that is the range that just changed.
So, we only want to have this macro run, if they change was it O19 or Q19.
So, we say if target dot address is equal to and dollar sign, zero, dollar sign, 19 then we want to clear out Q19, range Q19 dot clear contents.
I want to clear that validation, else if target dot address is equal to dollar sign, Q, dollar sign, 19 then range O19 dot clear contents and if all right!
So, nice little macro here, any time that you change a cell as macro run.
If the cell you just change is O 19, it'll clear Q 19.
If this cell you just change this Q 19, it'll clear O 19, again.
Very important here, this target address returns a text with the dollar signs but here's the really important thing and the thing that will crash your computer.
If you just left the macro like this, this marker gets running any time you change a cell, it also gets run any time that a macro changes a cell.
So, imagine you go change Q 19 and the macro clears out O 19.
That causes the macro to run again and this time it'll say, hey the thing that just got changed is 0 19, so clear out, what you just typed in Q 19, which causes the macro to run again, which clears out O 19 and it gets caught and endless loop.
So, anytime you have an event handler macro, one of these macros here on the worksheet code pane, we have to, anytime we change the worksheet, we have to say application dot and able events is equal to false.
You have to do that before you write to the work worksheet and then after you write to the worksheet, you want to change that back to true.
That allows the macro to work the next time someone changes a cell.
All right! So, there we go.
Make sure this one, gets changed back to true.
So, let's go to test.
Come here to Q 19 and I choose, and the other cell gets cleared out, come here choose and this cell gets cleared up.
Come to another cell, that's not one of those to type something and nothing happens, all right!
So, a little event handler macro.
X Calibre, also had a second question.
Is it possible to have zeros to a number for example, type 125 and have it enter a 125,000.
We talked about big numbered cells, which makes me think that he only wants it to happen in a certain place and not in other places.
If you needed it to happen in every worksheet.
Go check out podcast, 1817.
That shows how to do this for all cells in the worksheet, but I'm suspecting, it's just one certain range where the large numbers go.
All right! So, let's we're actually going to, just modify the code.
We just wrote to add that logic.
I'll switch back to VBA with [ alt F11 ], insert a few new lines here and I'm going to paste in the code, already had this written.
So, here's the range where we want this to work U55 to W60, use the intersect method in VBA to compare.
What just changed with that range?
If there's nothing in common, it's going to be equal to nothing.
So, back here if not that if this turns out to be true, then that means the cell that just changed is within the range.
Make sure that, what they typed is numeric and I do that by using the count function if they typed Abc, then the count will be zero.
So, make sure that we have a numeric value.
Turn off events, target value is equal to target value times a thousand, turn back on.
About to test.
This up here 123, nothing happens.
Here 123, it gets multiplied by a thousand and one thing you need to think about, is you know, what would happen if they would select more than one cell and our 123.
You know, you probably, I'm not sure, what you want to have happen there.
I would just to prevent that type of thing.
I would say if target dot cells dot count is equal to 1 then do all of this code otherwise, then I made clearing out a big range, and we don't want the code to run.
If you want to indent all these lines here, just select them all and press [ tab ].
I'll get indented perfectly.
Alright! So, that way everything still works, if we're dealing with a single cell but if we would select a big large range and press [ delete ] then the event handler won't.
All right! Here a couple of interesting questions from X Caliber.
Thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1850.
One cell, or the other.
We'll use the worksheet change macro to clear the other cell.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's questions sent in by X Caliber.
He has two cells, O19 and Q19.
There's a data validation in both, so you can choose from a list and when we choose from one, he wants to clear out the other and sure enough this is going to be done with VBA.
If you've never used to VBA before, we want to check two things.
First, make sure that you can use macros.
So, do [ alt + T ], followed by M followed by S, that would get you to macro settings.
By default, it's going to be at the first one, to save a little macros without notification.
You want to go to the second one, secondly make sure that your file is not saved as xlsx.
This is very common, it's the default.
This is the only file type, that is a hampered.
You cannot have macros in that xlsx.
You currently have an xlsx mixer, do file, save as and change the save as type to xlsm, click [ save ].
All right! Now, we gonna switch over to VBA.
Notice, that we are on sheet1 down here.
[ Alt F11 ] If you just have a big gray screen, make sure go to view, project Explorer or [ control R M ].
Now, we look for our workbook and right click on the sheet name and say view code.
All right! Now, we have two things here.
We have a drop down on the left hand side.
Only one option there, choose worksheet and they automatically choose selection, change this macro would get run every time we select a new cell.
That's not what we want to have, happen here.
So, we go in and change the right drop down to change.
This is the macro that will run every time, we change the cell and very nicely, the macro gives us a variable called target, that is the range that just changed.
So, we only want to have this macro run, if they change was it O19 or Q19.
So, we say if target dot address is equal to and dollar sign, zero, dollar sign, 19 then we want to clear out Q19, range Q19 dot clear contents.
I want to clear that validation, else if target dot address is equal to dollar sign, Q, dollar sign, 19 then range O19 dot clear contents and if all right!
So, nice little macro here, any time that you change a cell as macro run.
If the cell you just change is O 19, it'll clear Q 19.
If this cell you just change this Q 19, it'll clear O 19, again.
Very important here, this target address returns a text with the dollar signs but here's the really important thing and the thing that will crash your computer.
If you just left the macro like this, this marker gets running any time you change a cell, it also gets run any time that a macro changes a cell.
So, imagine you go change Q 19 and the macro clears out O 19.
That causes the macro to run again and this time it'll say, hey the thing that just got changed is 0 19, so clear out, what you just typed in Q 19, which causes the macro to run again, which clears out O 19 and it gets caught and endless loop.
So, anytime you have an event handler macro, one of these macros here on the worksheet code pane, we have to, anytime we change the worksheet, we have to say application dot and able events is equal to false.
You have to do that before you write to the work worksheet and then after you write to the worksheet, you want to change that back to true.
That allows the macro to work the next time someone changes a cell.
All right! So, there we go.
Make sure this one, gets changed back to true.
So, let's go to test.
Come here to Q 19 and I choose, and the other cell gets cleared out, come here choose and this cell gets cleared up.
Come to another cell, that's not one of those to type something and nothing happens, all right!
So, a little event handler macro.
X Calibre, also had a second question.
Is it possible to have zeros to a number for example, type 125 and have it enter a 125,000.
We talked about big numbered cells, which makes me think that he only wants it to happen in a certain place and not in other places.
If you needed it to happen in every worksheet.
Go check out podcast, 1817.
That shows how to do this for all cells in the worksheet, but I'm suspecting, it's just one certain range where the large numbers go.
All right! So, let's we're actually going to, just modify the code.
We just wrote to add that logic.
I'll switch back to VBA with [ alt F11 ], insert a few new lines here and I'm going to paste in the code, already had this written.
So, here's the range where we want this to work U55 to W60, use the intersect method in VBA to compare.
What just changed with that range?
If there's nothing in common, it's going to be equal to nothing.
So, back here if not that if this turns out to be true, then that means the cell that just changed is within the range.
Make sure that, what they typed is numeric and I do that by using the count function if they typed Abc, then the count will be zero.
So, make sure that we have a numeric value.
Turn off events, target value is equal to target value times a thousand, turn back on.
About to test.
This up here 123, nothing happens.
Here 123, it gets multiplied by a thousand and one thing you need to think about, is you know, what would happen if they would select more than one cell and our 123.
You know, you probably, I'm not sure, what you want to have happen there.
I would just to prevent that type of thing.
I would say if target dot cells dot count is equal to 1 then do all of this code otherwise, then I made clearing out a big range, and we don't want the code to run.
If you want to indent all these lines here, just select them all and press [ tab ].
I'll get indented perfectly.
Alright! So, that way everything still works, if we're dealing with a single cell but if we would select a big large range and press [ delete ] then the event handler won't.
All right! Here a couple of interesting questions from X Caliber.
Thank you for stopping by, we'll see you next time for another netcast from MrExcel.
