Okay, guys, so now we're going to discuss the workbook before close event. And as the name suggests, this before close event gets triggered whenever you try to close a workbook and it gets triggered before the workbook is closed. Okay, so actually whatever code you write in there will be executed before the actual closing of the workbook happens. And you can get it of course by going to your VBA editor, double clicking here on this workbook and selecting from this menu workbook and selecting before close from this menu up. So this is how you can get the before close event. Now, there is actually a parameter here called cancel.

And what this parameter does is that it allows you to actually cancel the closing of the workbook, so by default, its value is false. However, if we change its value to true if you set its value to true the The workbook is not going to close. Okay, so you can actually prank someone with that parameter. So let's see that. So I'm just going to set cancel equals true, okay, and then a message box, this workbook is not closing. Okay, so I'm just gonna try closing the workbook now and this code is going to be triggered when I tried to close the workbook.

So I'm just gonna close it here. And you can see here it messagebox this workbook is not closing, and the workbook did not close, okay? So by default, the value of cancel is false. However, if it is set to true in your procedure, or in whatever code you write in the workbook before close event, the workbook is not going to be closed. Okay, so this is the workbook before close event. We're gonna have another more practical example next.

Okay, guys, so now we're gonna have another example a more practical one. Actually for the workbook before close event. So this is actually the dashboard that I show as an example dashboard on my Excel dashboard scores. And you can see here that I've got a workbook before close event running on that dashboard. And what that does is that it calls a macro called show ribbon says to actually put Excel to the previous status of showing the ribbon, okay, because actually, the ribbon is hidden whenever this workbook is opened, and also the sheet dashboard is activated whenever this workbook is open. So I'm putting Excel back to its normal status of showing the ribbon and showing all the tools you know at the top.

Because what actually happens when this workbook is closed, and if if I don't show the ribbon or if I don't run the macro that is called show ribbon before closing the workbook is that the formula bar is not going to appear when we open Excel afterwards. So if we open even any Other workbook or any workbook in Excel or open a new Excel workbook, the formula bar is not going to show and, you know, despite the fact that it's very easy to show the formula bar by going to the View menu on the ribbon and just sticking the formula bar, some users might not know that. So if you're using a computer with other people as well, sharing the computer, you know, not having the formula bar showing will seem awkward, okay, so this is why I try to make it simple if somebody else is using a computer with me, okay, so I'm actually going to show you that practically.

So I'm actually gonna remove the show ribbon macro. This is a macro or a procedure that I've written in another module, I'm just calling it inside the workbook before close event. Okay, so I'm just going to remove it here, and I'm going to save my workbook and this way now the ribbon is hidden, and actually not the whole ribbon will be hidden when we open a new workbook, just the formula bar. So I'm just going to close here, the VBA editor, I'm going to close this workbook and save it Okay, now, I've opened a new workbook a blank workbook actually. And as you can see here, the ribbon has disappeared because I have not run the show ribbon macro. So you can actually show the formula bar again by clicking on the View menu on the ribbon here and checking the formula bar and then you can see the formula bar here, but actually, the formula bar has disappeared because I had not run the show ribbon macro.

So this is a practical example we're actually having the show ribbon macro run on the before close event can make Excel act normally or show the formula bar so as to not have things seem awkward for other users or so who are not very good with Excel are not familiar with this option of viewing the formula bar using the View menu here on the ribbon. Next we will see another example as well. Okay, so another thing that I use the workbook before close event for is to actually automate my work. So maybe if I'm building a dashboard, if I'm building a dashboard, and I'm using a lot of tabs in my dashboard here, so there's a row data there is the dashboard. And This stop is the one that should show to the customer. And this is the interface of the dashboard.

And there's an analysis tab. And there could be another tab here called lists. And sometimes I might build a dashboard that has lots of tabs, maybe six or seven. And I just want to close my workbook. And you know, normally the the other tabs except for the Dashboard tab should be hidden, and I just don't want to hide them manually, like right click and click on hide. I just want them to be hidden once they close the workbook.

Okay, so we're gonna see how to do that. So I use this a lot actually. So here on the before close workbook event, in addition to the coal show ribbon line here, which is actually going to show the ribbon and make things nice awkward for other Excel users on might write actually a procedure to hide my worksheets so as not to need to hide them manually before I close my workbook. Okay, I'm not hiding them in this case here because this is an example dashboard that I showed to the students so I need everything to be shown for them. But normally in real life, I would actually hide the other worksheets except for the dashboard worksheet, okay, so any other worksheets are using the background and they are considered the backstage of this dashboard, I would hide them. Okay, so I'm actually going to dim Ws work sheet, so I'm going to actually declare a worksheet object variable and I can actually say for each Ws in this workbook dot worksheets, next Ws so I can actually write some code to hide the worksheets except for the dashboard worksheet.

So if Ws Name is not equal to dashboard, then Ws dot visible equals excel sheet hidden and if so, this actually will hide my worksheet if its name is not dashboard. Okay, so let's try that. I'm just going to try here closing the workbook. Okay, so let's say I've been working on this workbook and I open all the tabs because I needed to do some maintenance on this dashboard, maybe add more data or do some amendments or whatever. Okay, and I'm just going to close my workbook here. So as you can see here, Before closing, all the worksheets have been hidden except for the dashboard worksheet.

Okay, so I'm gonna save it here. Let's say I'm gonna save it. Now I've reopened it again, as you can see here, all the other worksheets except for the dashboard worksheet are actually hidden right now. So if I need to start working again, I can unhide them, I can actually create another procedure as well just a normal procedure without any event that I would run, that would actually unhide all my worksheets if I want to start working and doing maintenance, so I can create another procedure that would actually unhide all my worksheets, right? So you can do that, I believe you can do that. I want you to attempt to do that on your own.

Okay, it's just gonna be a simple for each loop, looping through all the worksheets and unhiding them. And even if the worksheet is unhidden nothing's gonna happen. It's just gonna stay stay on hidden or visible. Okay, so I want you to attempt to do that. So that was the before close event. Thank you very much, guys for watching this video.

I'll see you on the next one.