Prevent User from Closing Workbook Using Excel VBA

Home More Excel (VBA) Training Videos

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 this training video on youtube




Home More Excel (VBA) Training Videos