Tuesday, 16 June 2015

Worksheet Event to AutoFill a Cell with a Default Value

How to Fill a Cell with a Default Value using Excel VBA Worksheet Event
Suppose we have a Worksheet where we are filling Employees Information as follows :



Here in the above Table , the Fields "Designation" , "Dept_Name" are Optional.  Now we want to enter a default value "Not Applicable" when you left a cell <Blank> or mentioned as 'NA'.

This can be done using the following WorkSheet Event as follows :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ErrMsg:

'Event applicable only in Columns 2 and Column 3
If (Target.Column <> 2 And Target.Column <> 3) Then Exit Sub

If (Target.Offset(-1, 0).Value = "" Or LCase(Target.Offset(-1, 0).Value) = "na") Then
Target.Offset(-1, 0).Value = " Not Applicable "
End If

ErrMsg:
If Err.Number = 13 Then Exit Sub
End Sub

Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts