Prevent User from Closing Workbook Using Excel VBA

Jonathan wants applicants to answer all questions in an Excel Workbook. If the user forgets to respond to a question, he is prevented from closing the workbook. We achieve this solution by using a ‘for loop’ in our VBA code which automates the complete process and guides the applicant to fill out the unanswered questions.

The complete Excel VBA code is given below:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each cell In Range(“C2:C7”)
If cell.Value = “” Then
MsgBox “Response required. Thank you!”, vbInformation, “Selection not made”
Cancel = True
Exit Sub
End If
End Sub

The code does the following:

  • It loops through all the cells that contain the answers
  • Cells that don’t contain a response cause the VBA code to give out an appropriate message
  • After receiving a response from the user to the displayed message the appropriate empty cell is selected and highlighted
  • No other window is displayed since the ‘Cancel’ button is automatically set to ‘True’
  • Now the user or applicant can completes his responses
  • Finally when the user closes the workbook he is asked to save the data

Watch the video:

One thought on “Prevent User from Closing Workbook Using Excel VBA

  1. This code is great on a single worksheey but how do you apply it across multiple named worksheets and cells . To ensure all questions have been answered before closing . Thank you for your help

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.