Announcement

Collapse
No announcement yet.

Worksheet_change(byval Target As Range) Function

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Worksheet_change(byval Target As Range) Function



    hi all, was wondering anyone can help me out with this function?

    ok, was trying to write a macro whereby whenever i press the save button, the macro would use the function above to check for any cells that have changed. have tried playing with the function above but then i was not able to capture the location of the cell. wanted to be able to capture the cell location and the value of the cell and then store them in an array. can anyone enlighten me with that?

    thanks in advance ...

  • #2
    Re: Worksheet_change(byval Target As Range) Function

    Hi. It may be just symantics, but the Worksheet_Change is an Event Macro, not a funcion. That is, it is a macro that will fire every time a cell in its sheet is changed (the macro is asscociated with only one sheet, because it MUST exist in the sheet's Private VBA Module.

    It does not report everything that has changed since some previous (historical) event.

    Does this help?
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

    Comment


    • #3
      Re: Worksheet_change(byval Target As Range) Function

      On rereading your question, IF you are trying to capture cell changes one at a time, then you can use it. Use
      Code:
      target.address
      and
      Code:
      target.value
      To get the cell address and its new value.
      Best Regards,
      Tom
      ---------------------------
      Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

      Comment


      • #4
        Re: Worksheet_change(byval Target As Range) Function

        As a sample, see the attached. It also captures a cell's previous value and who changed it and when (useful for multi-user situations).

        The code in the data worksheet's private module is:
        Code:
        Private Sub Worksheet_Change(ByVal Target As Range)
        ' // This macro writes cell change auditing data to the Change Log sheet //
            If Target.Cells.Count <> 1 Then Exit Sub
            ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = Target.Address
            ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = Target.Value
            If Target.HasFormula Then ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(0, 2) = "'" & Target.Formula
            ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(0, 5) = Format(Now, "ddMMMyy, hh:mm:ss")
            ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(0, 6) = Environ("username")
        End Sub
        
        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        '  // This macro writes current values everytime a new cell is selected //
        '  // The values are overwritten until a change event happens and the Worksheet_Change macro
        '  // files in the values around it (this filling column A value in that row)
        Dim OldVal As Variant, OldForm As String
            If Target.Cells.Count <> 1 Then Exit Sub
            ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 3) = Target.Value
            If Target.HasFormula Then
                ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 4) = "'" & Target.Formula
            Else
                ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 4) = ""
            End If
        End Sub
        I also find it useful to autofit the columns in the Change Log sheet, so this code goes in that sheet's private module.
        Code:
        Private Sub Worksheet_Activate()
            Columns.AutoFit
            Cells(Rows.Count, 1).End(xlUp).Select
        End Sub
        Attached Files
        Best Regards,
        Tom
        ---------------------------
        Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

        Comment


        • #5
          Re: Worksheet_change(byval Target As Range) Function

          Adendum:

          The above assumes changes to only one sheet are being captured (the sheet name is not being stored). If multiple sheets are being audited add a column for the sheet name and copy the data sheet code to each tracked-sheet's private module.

          Code:
          Private Sub Worksheet_Change(ByVal Target As Range)
          ' // This macro writes cell change auditing data to the Change Log sheet //
              If Target.Cells.Count <> 1 Then Exit Sub
              ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = Target.Parent.Name
              ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = Target.Address
              ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(0, 2) = Target.Value
              If Target.HasFormula Then ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(0, 3) = "'" & Target.Formula
              ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(0, 6) = Format(Now, "ddMMMyy, hh:mm:ss")
              ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(0, 7) = Environ("username")
          End Sub
          
          Private Sub Worksheet_SelectionChange(ByVal Target As Range)
          '  // This macro writes current values everytime a new cell is selected //
          '  // The values are overwritten until a change event happens and the Worksheet_Change macro
          '  // files in the values around it (this filling column A value in that row)
          Dim OldVal As Variant, OldForm As String
              If Target.Cells.Count <> 1 Then Exit Sub
              ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 4) = Target.Value
              If Target.HasFormula Then
                  ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 5) = "'" & Target.Formula
              Else
                  ChangeLog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 5) = ""
              End If
          End Sub
          Attached Files
          Best Regards,
          Tom
          ---------------------------
          Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

          Comment


          • #6


            Re: Worksheet_change(byval Target As Range) Function

            hi, really appreciate your help and samples its similar to what i am thinking ... thanks very much.
            Last edited by joshlan; January 27th, 2007, 03:17.

            Comment

            Working...
            X