Workbook Events - Workbook Open Event

Excel VBA for Beginners Events in Excel VBA
4 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.30
List Price:  €129.22
You save:  €36.92
£79.14
List Price:  £110.81
You save:  £31.66
CA$135.92
List Price:  CA$190.30
You save:  CA$54.37
A$153.17
List Price:  A$214.44
You save:  A$61.27
S$134.71
List Price:  S$188.59
You save:  S$53.88
HK$782.26
List Price:  HK$1,095.19
You save:  HK$312.93
CHF 90.56
List Price:  CHF 126.79
You save:  CHF 36.22
NOK kr1,076.34
List Price:  NOK kr1,506.92
You save:  NOK kr430.58
DKK kr688.47
List Price:  DKK kr963.88
You save:  DKK kr275.41
NZ$166.49
List Price:  NZ$233.09
You save:  NZ$66.60
د.إ367.19
List Price:  د.إ514.08
You save:  د.إ146.89
৳11,000.23
List Price:  ৳15,400.77
You save:  ৳4,400.53
₹8,335.05
List Price:  ₹11,669.40
You save:  ₹3,334.35
RM473.25
List Price:  RM662.57
You save:  RM189.32
₦139,656.03
List Price:  ₦195,524.03
You save:  ₦55,868
₨27,872.43
List Price:  ₨39,022.53
You save:  ₨11,150.09
฿3,637.73
List Price:  ฿5,092.97
You save:  ฿1,455.24
₺3,222.80
List Price:  ₺4,512.05
You save:  ₺1,289.25
B$497.67
List Price:  B$696.75
You save:  B$199.08
R1,888.37
List Price:  R2,643.79
You save:  R755.42
Лв180.67
List Price:  Лв252.95
You save:  Лв72.27
₩134,942
List Price:  ₩188,924.20
You save:  ₩53,982.20
₪366.06
List Price:  ₪512.51
You save:  ₪146.44
₱5,625.18
List Price:  ₱7,875.48
You save:  ₱2,250.29
¥15,118.78
List Price:  ¥21,166.90
You save:  ¥6,048.12
MX$1,663.06
List Price:  MX$2,328.35
You save:  MX$665.29
QR365.44
List Price:  QR511.63
You save:  QR146.19
P1,373.02
List Price:  P1,922.28
You save:  P549.26
KSh13,148.68
List Price:  KSh18,408.68
You save:  KSh5,260
E£4,723.53
List Price:  E£6,613.14
You save:  E£1,889.60
ብር5,694.53
List Price:  ብር7,972.57
You save:  ብር2,278.04
Kz83,612.73
List Price:  Kz117,061.17
You save:  Kz33,448.44
CLP$98,024.19
List Price:  CLP$137,237.79
You save:  CLP$39,213.60
CN¥722.49
List Price:  CN¥1,011.52
You save:  CN¥289.02
RD$5,928.03
List Price:  RD$8,299.48
You save:  RD$2,371.45
DA13,469.95
List Price:  DA18,858.47
You save:  DA5,388.52
FJ$225.25
List Price:  FJ$315.36
You save:  FJ$90.11
Q781.84
List Price:  Q1,094.62
You save:  Q312.77
GY$20,987.69
List Price:  GY$29,383.60
You save:  GY$8,395.91
ISK kr13,817.61
List Price:  ISK kr19,345.21
You save:  ISK kr5,527.60
DH1,010.57
List Price:  DH1,414.84
You save:  DH404.26
L1,763.81
List Price:  L2,469.40
You save:  L705.59
ден5,677.01
List Price:  ден7,948.05
You save:  ден2,271.03
MOP$807.66
List Price:  MOP$1,130.76
You save:  MOP$323.09
N$1,895.98
List Price:  N$2,654.45
You save:  N$758.46
C$3,689.21
List Price:  C$5,165.04
You save:  C$1,475.83
रु13,357.88
List Price:  रु18,701.57
You save:  रु5,343.68
S/371.02
List Price:  S/519.45
You save:  S/148.42
K383.60
List Price:  K537.05
You save:  K153.45
SAR375.02
List Price:  SAR525.04
You save:  SAR150.02
ZK2,548.47
List Price:  ZK3,567.96
You save:  ZK1,019.49
L458.83
List Price:  L642.38
You save:  L183.55
Kč2,334.38
List Price:  Kč3,268.22
You save:  Kč933.84
Ft36,425.52
List Price:  Ft50,997.18
You save:  Ft14,571.66
SEK kr1,059.50
List Price:  SEK kr1,483.35
You save:  SEK kr423.84
ARS$85,666.43
List Price:  ARS$119,936.42
You save:  ARS$34,269.99
Bs695.09
List Price:  Bs973.16
You save:  Bs278.06
COP$389,673.99
List Price:  COP$545,559.18
You save:  COP$155,885.18
₡50,509.65
List Price:  ₡70,715.54
You save:  ₡20,205.88
L2,474.33
List Price:  L3,464.17
You save:  L989.83
₲736,638.54
List Price:  ₲1,031,323.43
You save:  ₲294,684.88
$U3,781
List Price:  $U5,293.55
You save:  $U1,512.55
zł398.17
List Price:  zł557.46
You save:  zł159.28
Already have an account? Log In

Transcript

Hey guys, welcome back. So in this video, we're going to speak about workbook level events. So workbook level events happen whenever something happens on a workbook level, such as opening a workbook, or closing the workbook or saving a workbook. And that's amongst other things, actually. Okay, so how do we go to workbook events, so you can just go to the developer tab on the ribbon, click on Visual Basic, or you can hit Alton f 11 on your keyboard, and then you can just double click on this workbook here. Okay, so this workbook actually, is where you can write workbook events.

And then you can select from this menu here, you select workbook, and then actually the default event that gets started for you is actually the workbook open event. So on this event, what happens is that whatever code you put here, gets executed when your workbook gets open. And actually here is a full list of the events that you can use on a workbook level. Okay? Okay, so let's try to do something with that. So maybe message box.

Welcome to the workbook. Okay, so now whenever we open this workbook, we're going to have a message box that pops up. So I'm just going to close here with your basic and I'm going to save the workbook. I'm going to close the workbook. Okay, now have reopened my workbook. Okay, and as you can see here, I've got my message box popping up here.

Welcome to the workbook. Okay, so this enables you to execute any code that you would like, whenever you workbook that's open. Okay, guys, so maybe I want to do something else. Whenever I open the workbook, maybe I want to log the date and time on when it has been opened. Okay, and we will do that on sheet two, and the workbook. So I've created two columns here a date and time, and I want to just logged the date and time whenever it gets open.

Okay, so that would have a log here. So now I'm going to go to developer tab, click on Visual Basic, and then click on this workbook and make sure I'm in the workbook open event, select workbook from here and select open. And now I'm going to write some code to do that, and as part of that, I need to actually know what the last row is. First of all, I'm going to dim sheet two as worksheet. Okay, so I can give it a nickname and we can work with that nickname, and then set sheet two equals this workbook dot worksheets, sheet two. And then we actually need to get what the last row is.

So last row equals sheet two cells, rows dot count, comma one dot n to excel up dot row, and then we need to put the date and time in the row after the last row, row sheet two dot cells, aisle r plus one and one equals date and sheet two cells, r plus one and two equals time actually, there's a system function that is called time that is going to put the current time into a cell. So I'm just going to close your the workbook. We're going to close it here, save it, I'm going to reopen it. Okay, so now I've reopened the workbook. And as you can see here, the date and time that the workbook has been reopened on has been logged, if we close it again, and then we reopen it, you can see here that the date and time are going to be logged again as well.

Even if we hide the worksheet, this is still going to happen. So I'm going to close it here and reopen it again. And as you can see here, if I unhide cheat to the date and time has still been logged in you can actually put pretty much any kind of code that you would like to happen in the workbook open event. Okay, so whatever you want to put, you can put there. I've actually had a case where I needed actually to put the day of yesterday's date. So I use the day function and use the date function.

So I put in the cell, I said, the value of that cell equals day of date minus one. And then you know, there was another macro that was pulling up that day of yesterday's date and doing stuff with it. So that was actually a real case scenario, real life scenario. They all use the workbook open event in. Okay, so that's it for the workbook open event. Thank you very much 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.