Worksheet Events - Worksheet Change Event

Excel VBA for Beginners Events in Excel VBA
11 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€92.72
List Price:  €129.81
You save:  €37.09
£79.26
List Price:  £110.97
You save:  £31.71
CA$135.49
List Price:  CA$189.69
You save:  CA$54.20
A$153.49
List Price:  A$214.89
You save:  A$61.40
S$134.92
List Price:  S$188.90
You save:  S$53.97
HK$782.50
List Price:  HK$1,095.53
You save:  HK$313.03
CHF 90.16
List Price:  CHF 126.23
You save:  CHF 36.07
NOK kr1,085.06
List Price:  NOK kr1,519.13
You save:  NOK kr434.06
DKK kr691.90
List Price:  DKK kr968.69
You save:  DKK kr276.78
NZ$167.41
List Price:  NZ$234.39
You save:  NZ$66.97
د.إ367.19
List Price:  د.إ514.08
You save:  د.إ146.89
৳10,980.73
List Price:  ৳15,373.46
You save:  ৳4,392.73
₹8,335.04
List Price:  ₹11,669.39
You save:  ₹3,334.35
RM472.40
List Price:  RM661.38
You save:  RM188.98
₦139,532.04
List Price:  ₦195,350.44
You save:  ₦55,818.40
₨27,813.53
List Price:  ₨38,940.06
You save:  ₨11,126.52
฿3,638.31
List Price:  ฿5,093.78
You save:  ฿1,455.47
₺3,237.16
List Price:  ₺4,532.16
You save:  ₺1,294.99
B$501.53
List Price:  B$702.17
You save:  B$200.63
R1,888.66
List Price:  R2,644.21
You save:  R755.54
Лв180.76
List Price:  Лв253.07
You save:  Лв72.31
₩134,741.70
List Price:  ₩188,643.77
You save:  ₩53,902.07
₪367.71
List Price:  ₪514.81
You save:  ₪147.10
₱5,617.33
List Price:  ₱7,864.49
You save:  ₱2,247.16
¥15,132.45
List Price:  ¥21,186.03
You save:  ¥6,053.58
MX$1,658.17
List Price:  MX$2,321.51
You save:  MX$663.33
QR364.88
List Price:  QR510.84
You save:  QR145.96
P1,378.06
List Price:  P1,929.34
You save:  P551.28
KSh13,256.67
List Price:  KSh18,559.87
You save:  KSh5,303.20
E£4,744.14
List Price:  E£6,641.99
You save:  E£1,897.84
ብር5,687.29
List Price:  ብር7,962.44
You save:  ብር2,275.14
Kz83,433.85
List Price:  Kz116,810.73
You save:  Kz33,376.88
CLP$98,290.17
List Price:  CLP$137,610.17
You save:  CLP$39,320
CN¥722.08
List Price:  CN¥1,010.95
You save:  CN¥288.86
RD$5,924.54
List Price:  RD$8,294.60
You save:  RD$2,370.05
DA13,479.97
List Price:  DA18,872.49
You save:  DA5,392.52
FJ$225.66
List Price:  FJ$315.94
You save:  FJ$90.27
Q780.13
List Price:  Q1,092.22
You save:  Q312.08
GY$20,949.30
List Price:  GY$29,329.86
You save:  GY$8,380.56
ISK kr13,941.60
List Price:  ISK kr19,518.80
You save:  ISK kr5,577.20
DH1,013.74
List Price:  DH1,419.27
You save:  DH405.53
L1,764.37
List Price:  L2,470.19
You save:  L705.82
ден5,702.11
List Price:  ден7,983.18
You save:  ден2,281.07
MOP$806.29
List Price:  MOP$1,128.85
You save:  MOP$322.55
N$1,909.24
List Price:  N$2,673.01
You save:  N$763.77
C$3,682.20
List Price:  C$5,155.22
You save:  C$1,473.02
रु13,347.49
List Price:  रु18,687.02
You save:  रु5,339.53
S/372.08
List Price:  S/520.93
You save:  S/148.85
K382.87
List Price:  K536.04
You save:  K153.16
SAR375.15
List Price:  SAR525.22
You save:  SAR150.07
ZK2,481.24
List Price:  ZK3,473.83
You save:  ZK992.59
L460.94
List Price:  L645.33
You save:  L184.39
Kč2,346.52
List Price:  Kč3,285.22
You save:  Kč938.70
Ft36,532.34
List Price:  Ft51,146.74
You save:  Ft14,614.40
SEK kr1,069.05
List Price:  SEK kr1,496.71
You save:  SEK kr427.66
ARS$85,802.56
List Price:  ARS$120,127.02
You save:  ARS$34,324.45
Bs691.35
List Price:  Bs967.92
You save:  Bs276.56
COP$386,901.39
List Price:  COP$541,677.43
You save:  COP$154,776.03
₡50,320.66
List Price:  ₡70,450.94
You save:  ₡20,130.27
L2,469.73
List Price:  L3,457.73
You save:  L987.99
₲739,686.52
List Price:  ₲1,035,590.72
You save:  ₲295,904.20
$U3,756.16
List Price:  $U5,258.77
You save:  $U1,502.61
zł399
List Price:  zł558.62
You save:  zł159.61
Already have an account? Log In

Transcript

Hey guys, welcome back. So in this video we're going to discuss the worksheet change event. So the worksheet change event gets triggered whenever a cell in your worksheet gets changed either by yourself as user or by an external link. So any change really, that happens to a cell in your worksheet is going to trigger the worksheet, change event. Okay, so let's illustrate that by a simple example. So I've got here a sheet called worksheet change event.

I'm just going to right click on it and click on View code and then go to worksheet on this menu here and then click on Change event. Okay, and then I'm just gonna delete this worksheet selection change event because we don't need it. And now I'm just gonna put a message box here, you made a change on your worksheet. Okay, this message box is going to be triggered whenever I make a change on email. selling my worksheet This is different from the selection change though the selection change gets triggered whenever you select a different cell, but you see now it's not being triggered because this is not what triggers it what triggers it is changing something on a sheet. Okay, so you change the contents of a cell, then this message box gets triggered, you made a change on your worksheet.

Okay, so this is how you can trigger the worksheet change event, even if you delete the contents of the cell. This also triggers the worksheet change event. Okay, so let's have a more sophisticated example. Okay, so let's say on this column on column B, what I want to do is when I put a value, I want it to be multiplied by a million, okay, so let's say on this column, you need to be entering values that are millions, okay? And you just want if you want to put 2 million you just put a two and then Excel just multiplied by million and it will be 2 million, the value will be 2 million, but you don't want to type two and then six zeros or two multiplied by 10 raised to the power six each time you input the value, okay? Or so you don't want to multiply the value by a million.

You don't want to type a lot of zeros, you just want to put the number, okay? And then Excel will multiplied by 1 million. Okay, so this is our objective on this column on column B, we want to do that. Okay, so let's see how we can do that. And we're going to face some obstacles, actually, let's see how we can do that. So I'm going to go here to my worksheet change event.

And then I'm going to start typing if target because this has a target parameter and the target is basically the cell that you changed, okay? So if target dot column equals two, okay, that means it's in column B. It's in second column, then let's put a bar and if here, okay, so if target column equals two, what do we want to do? We actually want to multiply our value by a million, okay? So target value is equal to target dot value, okay? And then multiplied by, we can't put a 10 raise to the power six here, we cannot do that we will get an error.

You can't do that in VBA. What you want to do is to actually call a worksheet function, okay? So you can actually call a function, a normal Excel function, normal worksheet function from Excel VBA. So how do you do that? Okay, so this is something new, that we're going to learn here. application, you can write application dot worksheet, function, and then dot and then you choose the function there's a function called power in Excel, okay, this is a function in normal Excel called power.

Okay, and what this function does is it accepts two inputs, the first input is basically the number that you that you want to raise to a certain power since the base, okay? So it's going to be 10. And then the power itself is going to be six. So that means that the value of the cell that we're changing is going to be multiplied by 10 to the power six the target value because target value multiplied by 10 to the power six. Okay, so let's see what's going to happen if we just leave the code is that because this is not going to work as we want, so let's see what's gonna happen here. Let's say I want to write 2 million, okay, so I want to write just two and then Excel will just convert it to 2 million or make it 2 million.

Okay, so I'm gonna write two here. And look at this number. It's a very big numbers two multiplied by 10 to the power 306. So that's it. To multiply by 10 to the power 306 Oh, that's not the number that we wanted. We just wanted 10 2 million.

That's it. We don't we didn't want this big number. So what's happened here? What's happened? Well, what's happened is that the change that you made in the cell has been actioned recursively. Okay, so it's like going to recursive action because you changed the content of the cell, and that triggered a change and then the change triggered another change and another change another change.

And and excel kept actually multiplying the value by 10 to the power six, you know, until it reached actually the the biggest number that an Excel cell can handle in this case, actually, biggest number than Excel cell can handle is I think it's 10 to the power 307 or something like that. But this is the biggest number that the cell could handle. So it just kept on multiplying the number the result by tend to the power six is like to buy 10 to the power six, and then it takes the product of that and then multiplies it by 10 to the power six, and then it takes the product of that and then multiplies it by 10 to the power six and keeps on doing that until reaching the highest value that Excel can handle. Okay, so this is what basically happened, that number kept on multiplying itself by 10 to the power six.

Okay, so to help you understand what happened better, let's step through the code, go through the code step by step. Okay. So I'm actually going to delete the content of the cell here. And you can see here that the event still has been triggered, but we're having a zero because it's multiplying zero by 10 to the power six, and so it's resulting a zero. And even with the recursive reaction, it's just resulting a zero. So I'm just gonna go back here and I'm going to put a breakpoint in my code at this line.

So that the We'll stop executing at this line here and then we're going to start executing it manually. So I'm gonna put two in here. And you can see here there's the event has been triggered, but it's the the code stopped at this line at the breakpoint that I set. Now I'm gonna press f8 to continue executing the code. So the first time it's been executed, it's resulted in 2 million. Now this has triggered a change event and then it it's resulted in multiplying it by another 10 to the power six is now attend to the poor 12.

And if we keep pressing f8 is now tend to the power to multiply by 10 to the power 18. And then it's 10 to the power 24. And then it's then to a port 30 and 36. So I'm executing the code manually using f8. This is actually one of the best ways to troubleshoot events event codes, in case you're having a problem with the with VBA code that you've written in the event. Okay, put a breakpoint and then start executing the code manually.

Okay? So we're doing that you can see here the code is still being executed and the event is still being triggered. It's a recursive reaction, okay? It's like a chain reaction is just a change, you know, triggering a change, which triggers a change, which triggers a change. And this keeps happening until we, actually, where are we going to stop, we're actually going to stop at the highest number that this cell could handle. Okay?

So if there was a higher numbers, Excel would still, you know, keep on multiplying the result by 10 to the power six, okay, then we, and the if, and so, actually, Excel still wants to do it. But actually, this is the, the highest number that a cell can handle. So Excel wants to still execute the change, but there's no we can't go any higher on the cell because this is the highest number that we can go on the cell. Is this crazy? Okay, so I'm just gonna stop all that craziness and Now we're going to see how we can solve that problem. So I'm going to remove the breakpoint.

And let's see how we can solve that problem. Okay, so this is where disabling events is handy. This is where disabling events becomes handy. So actually, if you add an application dot enable events, the line of code here application dot enable events equals false here, before we execute this line of code, and then we could actually enable the events after the if statement, okay, so before ending the sub, where at any point that we find convenience, convenient, okay, so application dot enable events equals true, we will not have this problem. Okay, so let's see that again. Let's write to and you can see here we've just got 2 million is two with six zeros beside it.

So What happened here is we actually checked that we're in the second column here, and then we disable the events, okay? And we executed that line of code, which is multiplying two by 10 to the power six. Okay, and then we enabled it back after, there was nothing to trigger the event. So this is what happened. Now if you write any number here five, for example, we're going to have 5 million a proper 5,000,050 are going to have 50 million, just 50 million, not a, you know, 50 by 10 to the power 307 or something like that. Okay, so this is where the disabling events comes handy.

Okay. This is a simple example. You need to make sure to use your tool, your tools, right, and you need to know when to disable the events in order to get the results that you Want Okay, so thank you guys for watching this video. I'll see you on the next one

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.