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

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