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.
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.