Posts

Showing posts from August, 2009

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,A…

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