Wednesday, 29 October 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

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts