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: