Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Worksheet_change(byval Target As Range) Function

  1. #1
    Join Date
    21st January 2007
    Posts
    6

    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 ...

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    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.

  3. #3
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    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
    VB:
    target.address 
    
    
    and
    VB:
    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.

  4. #4
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    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:
    VB:
    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.
    VB:
    Private Sub Worksheet_Activate() 
        Columns.AutoFit 
        Cells(Rows.Count, 1).End(xlUp).Select 
    End Sub 
    
    
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    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.

  5. #5
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    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.

    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    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.

  6. #6
    Join Date
    21st January 2007
    Posts
    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 at 03:17.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: December 6th, 2008, 06:58
  2. Replies: 5
    Last Post: January 21st, 2007, 19:23
  3. Worksheet_Change Event Target Location
    By anselmi_r in forum EXCEL HELP
    Replies: 3
    Last Post: February 28th, 2006, 10:59
  4. Replies: 2
    Last Post: October 28th, 2004, 19:20
  5. Replies: 2
    Last Post: June 23rd, 2004, 23:46

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno