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

If Err.Number = 13 Then Exit Sub
End Sub

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts