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.

Reading an Excel in VBScript

Below is a piece of code which can be used to read excel sheet and the values in the row or column.

Option Explicit

Dim myExcel,strExcelPath,objSheet
Dim intRow,strRow2Col1,strRow2Col2

Set myExcel = CreateObject("Excel.Application")
strExcelPath = "C:\Book2.xls"

myExcel.WorkBooks.Open strExcelPath
Set objSheet = myExcel.ActiveWorkbook.Worksheets(1)

'Assuming that the first row will contain the name of the column
intRow = 2
Do While objSheet.Cells(intRow, 1).Value <> ""
    strRow2Col1 = objSheet.Cells(intRow, 1).Value
    strRow2Col2 = objSheet.Cells(intRow, 2).Value
'Increment the control to the next row
    intRow = intRow + 1
Loop

myExcel.ActiveWorkbook.Close
myExcel.Application.Quit