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 ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
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 ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.