Hey guys, welcome back. So in this video we're going to discuss the workbook before save event. So the workbook before a save event, and as the name suggests, it actually gets fired before saving a workbook. Okay, and that means that it gets fired just after you hit the Save button on your keyboard. So after you press Ctrl on s, and before the actual saving, this macro gets fired, okay, so to apply that event, you need to go to the developer tab on the ribbon, click on Visual Basic, okay, and make sure you are in this workbook module here and select workbook from this menu, and then from the menu on the right here you're gonna select the before save event, I'm just gonna delete the workbook open event because we don't need it. And as you can see here, this before save event has actually two parameters.

There is the Save As you will And there is the cancel parameter and they are both Boolean and that means that the both accept to or false values only. Now the Save As you are a parameter what this controls is that it controls whether you get the Save As dialog box when trying to see the workbook that is not saved already or that is open as a read only workbook So, what I'm meaning is when you open a new workbook Okay, this is a new workbook opened. And you can see your Excel gives it a temporary name of book one when you press Ctrl N s on your keyboard, you get the Save As dialog box or this supposedly Save As dialog box and you click Browse and then you can start saving Okay, so this is the Save As dialog box. Supposedly, this option should be able to enable it or disable that okay, disable getting the the safe As dialog box, supposedly you should be able to set the value of that to false.

And then the Save As dialog box would not appear. So we can actually try that. So I'm just going to go to book one here, click on this workbook here, and then click on workbook here and click on before save. And then if I put the Save as UI equals false, supposedly, that should disable the Save As dialog box from appearing. So if I go to the book one again here and press Ctrl N s, actually, I'm still getting the Save As dialog box, I'm still getting the Save As option. I've researched that on the internet, and a lot of people are actually reporting that it's not working, so we're not going to discuss it any further.

The second parameter here that we're going to explain is the cancel parameter. And what this parameter does is that if it is put equals to true, then your workbook will not be saved. So I'm actually gonna write cancel equals true here. So this is going to be my code basically just cancel equals true and this code should be run whenever I tried to save my workbook. So I'm just going to write my name here. Okay.

And this cell I'm going to try to save the workbook and actually you will not be saved. So if pressed Ctrl N so normally should be saved it will not be saved even if I try to close and press Save here, the workbook gets closed however, let's try to open it again. Okay, so after opening the workbook again, my name has not been saved in this cell, okay, and I've not been able to save the workbook and even when I go to this workbook here actually, even the procedure that I have written which is for the before save event has not been saved. Nothing has been saved on the workbook, okay. So when you set cancel equals to true, your workbook is not going to be Save okay so let's have an example here for the before save event. So actually let's say that this is where the user should enter his or her name and cell b1 and I want to make sure that the enter their name and if the name is not entered, if the cell is blank then the workbook is not going to be saved if they if they try to save it, then it will not be saved and they will have a message box that says workbook has not been saved Please enter your name.

Okay, so to do that, we're going to start writing an if statement. So if this workbook dot worksheets sheet one dot range D one is equal to blank then and if cancel is equal to true, so there is no no saving that's going to be done and then message box cannot save. Please enter your name okay. So they are going to have a message box that says cannot see Please enter your name. So now if Try to save the workbook and there is no name entered here it's blank, but try to save cannot save, please enter your name. So I press Ctrl.

And as Sonny got this message box, however, if I put my name here, I tried to save the workbook, it's going to save and I have not been given the message box. If I close it and reopen it, I should have my name there. So I'm going to do that I'm going to close it. Now I've reopened it. And as you can see here, I've got my name in the in cell b1, so I've been able to save the workbook successfully. Okay.

So this is how the before save event works. It actually triggers it actually executes any code after you press Save or press Ctrl S on your keyboard, okay, or hit file and save or click on this button. Save here and before the actual saving and through that event, you can actually disable the saving Have a workbook if you need to, by setting cancel equals to true. Okay, so thanks guys for watching this video. I'll see you on the next one.