Wednesday, October 29, 2014

Work Sheet Change Event to Save a Workbook on Changing a Cell Value in a Specified Range

How to Save Workbook on Changing a Cell Value in a Specified Range
The following worksheet event triggers and saves the Workbook when you make change to the cells in any specified Range ( say , "C5:C10" ).
Private Sub Worksheet_Change(ByVal Target As Range)
''Check to see if the Changed Cell is exists with in Specified range
    If Intersect(Target, Range("C5:C10")) Is Nothing Then

    Exit Sub ' Exit if Not

    Else
    ActiveWorkbook.Save

    End If

End Sub


Example II :
The following WorkSheet Event is used to do the duplicate entry in Main sheet while you making entry in the Sub Sheet.

Private Sub Worksheet_Change(ByVal Target As Range)    
    On Error GoTo ErrorHandle:
    If Intersect(Target, Range("A:C")) Is Nothing Then

        Exit Sub ' Exit if the Entry is not happening in Specified Columns

    Else
    Rng = Target.Address 'The Target Cell Address
    RngVal = Range(Rng).Value
        If RngVal <> "" Then
            ThisWorkbook.Sheets("Main").Range(Rng).Value = RngVal
        End If
    End If
    
ErrorHandle:
    If Err.Number = 13 Then Resume Next

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