CODEWITHSUNDEEP
vbaexcel
Namaro
Namaro

Reputation: 1

Linking an Excel Sheet to another in the same workbook using VBA

I'm trying to keep track of what is been done in my workbook, so I would like to have a copy of the sheet with comments added to it with date of change and user that changed it(don't really care what they change but I want to know what cells they change with dates and username), yes I know you can use copy and paste it as link, but the VBA script that I have right now doesn't recognize the changes made in the original sheet Code Below:

    Private Sub Worksheet_Change(ByVal Target As Excel.range)
'If Target.Column <> 1 Then Exit Sub
Dim ccc As range
Dim comment As String
Application.ScreenUpdating = False
Application.Calculation = xlManual

comment = ("Cell Last Edited: ") & Now & (" by ") & Application.UserName
Target.ClearComments
    For Each ccc In Target
    range(ccc.Address).AddComment comment
    Next ccc

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub

In which works really nice, Now to be clear I want script that copies the original sheet to another one and if someone make change then copy or modified the copy sheet and add the comment with date and username

Any suggestion would be great and thanks in advance :D

Upvotes: 0

Views: 228

Answers (2)

paul bica
paul bica

Reputation: 10715

Not sure if keeping a duplicate of the sheet is the easiest way: what if users insert new rows or columns (thousands) or cells within existing data, or charts, etc

I'd suggest keeping a log of changes similar to this

  • Given a WorkBook with 2 sheets (Sheet1 and Sheet2)
  • Sheet1 contains the data
  • Sheet2 will contain a log with 4 columns: Date, User Name, Cell, New Value

then Sheet1 will contain this VBA code:

Option Explicit

Private logLine As Long

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge < 1000 Then    ' don't log deletion of all cells on Sheet1

        Dim editedCell As String

        ' get the clean cell address ("A1" instead of "$A$1")
        editedCell = Target.Address(RowAbsolute:=False, ColumnAbsolute:=False)

        With Sheet2     ' generate all log lines on Sheet2

            logLine = logLine + 1   ' move to the next row on Sheet2

            ' Column 1: Date and Time
            .Cells(logLine, 1).Value2 = Format(Now, "ddd mmm dd, yyyy hh:mm:ss")

            ' Column 2: User Name
            .Cells(logLine, 2).Value2 = Application.UserName

            ' Column 3: link to the edited cell, also showing the cell itself
            .Hyperlinks.Add _
                Anchor:=.Cells(logLine, 3), _
                Address:=vbNullString, _
                SubAddress:="Sheet1!" & editedCell, _
                TextToDisplay:=editedCell

            ' Column 4: the new value
            .Cells(logLine, 4).Value2 = Target.Value2
        End With
    End If
End Sub

On Sheet2 you'll have the 4 columns:

        Date-Time          User Nane   Cell    New Value
Wed Jun 03, 2015 01:07:43   User1       A1      Test 1
Wed Jun 03, 2015 01:07:48   User2       B3      Test 3
Wed Jun 03, 2015 01:07:52   User5       D2      Test 7

Upvotes: 1

Tango_Mike
Tango_Mike

Reputation: 104

Why do you need to have a VBA code in the first place? You could simply use the already built-in feature of tracking changes in Excel (Review - Changes - Track Changes). Once users make their changes, save and close the workbook; you could just go to the "Highlight Changes" line within the "Track Changes" feature and select "List changes on a new sheet". This will create a new sheet and will show all the necessary info (action number, date, time, who, change, sheet, range, new value, old value, etc).

Upvotes: 0

Related Questions