Close Workbook in VBA Without Saving

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:

Graphical user interface, application

Description automatically generated

The code for closing out the Workbook is pretty simple and it goes like this:

This is what the code looks like in the module:

Graphical user interface, text, application, email

Description automatically generated

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:

Graphical user interface, text, application, email

Description automatically generated

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:

Now let us suppose that we have a list of NBA players in our sheet:

Graphical user interface, text, application

Description automatically generated

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:

Graphical user interface, application, Word

Description automatically generated

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:

Graphical user interface, text, application, email

Description automatically generated

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.

Graphical user interface, application

Description automatically generated with medium confidence

If we add anything to our Workbook now, and simply click on our button, we will not be prompted with the typical question:

Graphical user interface, text, application

Description automatically generated

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.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba