Tuesday, June 16, 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 ; 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.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog