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”
cell.Select
Cancel = True
Exit Sub
End If
Next
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: