Hello Excellers and welcome to another Macro Monday blog post.
Today let’s look at forcing a user to populate a specific cell in the workbook before they are allowed to close it.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Mondays Series you can do so by clicking on the link below.
How To Excel At Excel – Macro Mondays Blog Posts.
What Does The Macro Do?
This Macro uses the BeforeClose event, which is triggered when the user attempts to close the workbook. The Macro Checks the target cell to see if it empty, if it is the close file process is cancelled, if it is not empty then the workbook saves and closes.
How Does It Work?
FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab, in the project window find your workbook name – click the + sign and to see all of the worksheets. Click ThisWorkbook and select the BeforeClose event in the drop down list.
Step 2. This step checks to see if our target cell (B10) is blank.
Step 3. This is the step that determines if the workbook is closed. If the cell = BLANK then the workbook is not closed and the user is warned with a message box which states “Please Populate cell B10”.
Step 4. If cell B10 is not BLANK then the workbook and be saved and closed.
Step 5. Test your Macro!!
Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.
Other Posts In The MACRO MONDAYS SERIES
Macro Mondays – Change All Pivot Table Filters At Once With A Macro
Macro Mondays – Unlock ALL Cells In An Excel Workbook With VBA
Macro Mondays- Excel Generate Random Numbers With A Simple Excel Macro
Macro Mondays – Unprotect Multiple Excel Worksheets At Once With A Simple Excel Macro
Macro Mondays – Unlock ALL Cells In An Excel Workbook With VBA
Macro Mondays- Excel Generate Random Numbers With A Simple Excel Macro
Macro Mondays – Limit The Scroll Area Or Range Movement In An Excel Worksheet
Macro Mondays – Delete All Rows That Contain A Specific Text String
Macro Mondays – Save An Excel Workbook When When A Specific Cell Or Cells Change
Macro Mondays – Print Only Specified Worksheets In My Excel Workbook
Macro Monday – How To Trim Spaces From All Cells In A Selected Range On Your Excel Worksheet
Macro Mondays – Select And Format All Cell That Contain A Formula In Your Excel Workbook
Macro Mondays – A Macro To Delete All Blank Rows In Excel Worksheet
Macro Mondays – How To Delete Your Personal Macro Workbook
Macro Mondays – Remove Personal Information From Excel Using Some Simple VBA
Macro Mondays – Moving And Undocking The Immediate Window In The Visual Basic Editor
Macro Mondays – What are the Three Windows In The Visual Basic Editor For?