Two questions about VBA in excel: 1. Return a column name and return old value of a cell-CodePudding

I'm creating a spreadsheet with logdetails of another sheet with information that is changing constantly and I have to keep tracking the changes. I was able to record into the logdetails spreadsheet part of the changes

but not the column name (based on the cell address and the old value).

Here it is my VBA code so far.

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

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, 3).Value = Target.Value
Sheets("logdetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Environ("username")
Sheets("logdetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 5).Value = Now
Sheets("logdetails").Columns("A:H").AutoFit

Application.EnableEvents = True

End If

End Sub

CodePudding user response：

First you need to save the old value somewhere through workbook event. The variable lastRng bellow will save the value of every active cell and it will be restored in case of change

Dim lastRng

Private Sub Workbook_Open()
    Set lastRng = ActiveCell
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    lastRng = Target.Value
End Sub

After, you add the next two lines

Sheets("logdetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Target.Address
Sheets("logdetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = lastRng

to complete your table as you desire. I didn't understand very well what you means by column name, but if you want the letter instead column number or cell address, you can find good solutions here in this question to convert one in another

All in all, your consolidated code will be like this:

Dim lastRng

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    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 = Target.Address
        Sheets("logdetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = lastRng
        Sheets("logdetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Target.Value
        Sheets("logdetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Environ("username")
        Sheets("logdetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 5).Value = Now
        Sheets("logdetails").Columns("A:H").AutoFit
        
        Application.EnableEvents = True

    End If
End Sub

Private Sub Workbook_Open()
    Set lastRng = ActiveCell
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    lastRng = Target.Value
End Sub