Making cells mandatory in Excel

Recently I read some article in Martin Flower’s blog where he is pointing to “Excel” as the most popular programming language. Indeed it is ! I very much agree with that. It’s just a powerful tool which allows one a freedom of implementation in VBScript.
I am just sharing my experience with Excel where I had to make some cells mandatory as a part of filling a form. Hope you will be benefited.

Option Explicit
Dim Mandatory As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub

Select Case Target.Address
Case “$A$5”, “$A$10”, “$A$15”, “$A$20”, “$A$25”, “$A$30”
Set Mandatory = Target
Case Else
If Not Mandatory Is Nothing Then
If Mandatory = “” Then
Mandatory.Select
MsgBox “You cannot leave this cell blank”
End If
End If
End Select

End Sub
The above macro will make the cells A5,A10,… mandatory and pressing TAB will prompt user a message “You cannot leave this cell blank” and the focus will remain in the cell.
In case of any issues do drop in a comment or mail to me.

Back to Top
%d bloggers like this: