Argthtjtr

I create a log history worksheet and save the change detail of other worksheets.

Dim oldValue As Variant



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim sSheetName As String

sSheetName = "Data"

If ActiveSheet.Name <> "LogDetails" Then

Application.EnableEvents = False

Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & "-" & Target.Address(0, 0)

Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue

Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Formula

Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")

Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now



Sheets("LogDetails").Columns("A:D").AutoFit

Application.EnableEvents = True

End If

End Sub



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

oldValue = Target.Formula

End Sub

That works for a single cell, e.g.:

If A1 stores "ABC" and changes to "123", Log Detail will save the Cell address, old value, new value, username and date/time.

The big problem is when I select a whole column, such as all of column(B). It will get the error

"type not match".

I know the problem is

oldValue = Target.Value

How can I save the change of a column?

I'm not entirely sure if that solves your problem, but try the following modification to your Workbook_SheetSelectionChange procedure:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Count > 1 Then

        Target(1).Select

        Exit Sub

    End If

    oldValue = Target.Value

    oldAddress = Target.Address

End Sub

Every time the user selects more than one cell the event procedure will change that selection (which raises another change event, with a single-cell target this time) and exit without doing anything else. The criterion for when this selection change should happen can of course be refined to allow for a more specific behavior.

This should make it much harder for the average user to intentionally or accidentally modify more than one cell at a time.

To address questions from your comments:

the undo function of excel cannot be used

This is true. Excel doesn't know how to reverse the actions your code has taken. You need to build this functionality yourself. See this question + accepted answer.

the formula change show in log sheet cannot not show properly, it will show 0 or #Value!

Yes, that's by design. With the line

Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Formula

you tell Excel to set that cell's value to a formula. Which Excel then automatically tries to evaluate. (Leading to the errors you're experiencing)

Try the following:

' Prepend the formula with an apostrophe

Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = "'" & Target.Formula

This will force Excel to treat the cell value as text and thus it will simply show the formula without evaluating it.

copy and paste a range only show the first cell changes, is it cannot be fix?

This is due to oldValues being an array while you only ever access its first value. See my implementation:

Option Explicit



Dim oldValues As Variant



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)



    Const LogSheet As String = "LogDetails"



    If Sh.Name = LogSheet Then Exit Sub



    Application.EnableEvents = False



    With Worksheets(LogSheet)



        Dim idxRows As Long

        For idxRows = 1 To Target.Rows.Count



            Dim idxCols As Long

            For idxCols = 1 To Target.Columns.Count



                Dim ChangedCell As Range

                Set ChangedCell = Target.Rows(idxRows).Columns(idxCols)



                Dim LogRow As Long

                LogRow = .Range("A" & Rows.Count).End(xlUp).Row + 1

                Dim LogRange As Range

                Set LogRange = .Range(.Cells(LogRow, 1), .Cells(LogRow, 5))



                LogRange(1).Value = Sh.Name & "!" & ChangedCell.Address(False, False)

                LogRange(2).Value = "'" & oldValues(idxRows, idxCols) ' error here when pasting a range of different size than has been selected before pasting

                LogRange(3).Value = ChangedCell.Formula

                LogRange(4).Value = Environ("username")

                LogRange(5).Value = Now



            Next idxCols



        Next idxRows



        .Columns("A:E").AutoFit



    End With



    Application.EnableEvents = True



End Sub



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    oldValues = Target.Formula

End Sub

This has the weakness that when a user copies multiple cells and then selects a single cell and pastes, it will error out, due to mismatching indices. (It works when you copy, e.g. 3 cells in a row, then select 3 other cells in a row and paste.) Not sure how to avoid that. We'd need to capture the size of the pasted range to update oldValues accordingly. As Excel doesn't expose a Workbook_SheetBeforePaste event, that seems rather tricky.