As you are probably aware, Visual Basic for Applications (VBA) is a very powerful and useful tool. Its most convenient feature is the automatization of our work. With it, we can manipulate cells, sheets, and workbooks as a whole.
In the example below, we will show how to close our Workbook without saving the changes.
Close Workbook in VBA Without Saving
To close the workbook through the VBA, we first need to open the VBA. We will do this by clicking ALT + F11 on our keyboard. On the window that appears, we will right-click on the left window and select Insert >> Module:
The code for closing out the Workbook is pretty simple and it goes like this:
|
1 2 3 4 5 |
Sub CloseWorkbook() Application.DisplayAlerts = False ActiveWorkbook.Close Application.DisplayAlerts = True End Sub |
This is what the code looks like in the module:
The first part of the code takes care of displaying the error message on our window, as the messages are set to false.
A second line of the code: ActiveWorkbook.Close will only close our Workbook. To close the Workbook without saving any changes, we need to step into the code and press space after this line of code. These are the options we then have at our disposal:
We can choose to SaveChanges or not, to automatically create the name of the file, or route the workbook (send it somewhere). We only need the first option, so we will add it to our line of code, and the code will look like this:
|
1 2 3 4 5 |
Sub CloseWorkbook() Application.DisplayAlerts = False ActiveWorkbook.Close SaveChanges:=False Application.DisplayAlerts = True End Sub |
Now let us suppose that we have a list of NBA players in our sheet:
Creating Button and Adding Macro
We will add our code to the worksheet, by going to the Developer tab, then going to Controls >> Insert >> Form Controls >> Button:
We will have a cross sign as our mouse pointer that will allow us to draw our button. As soon as we define the boundaries of our button, the following window will appear:
We can clearly see our macro in the list of macros that can be assigned to our button (CloseWorbook). We will choose it, and click OK.
On the button that appears, we will change the name and this is what we will end up with.
If we add anything to our Workbook now, and simply click on our button, we will not be prompted with the typical question:
That we get when we are implementing changes in a “regular” way. If we execute our code, our Workbook will be closed without question and without any changes being saved.