Need to expand on a macro that captures from and to changes onto a separate log

[sspatriots]

Well, everything seems to be working with this code I found on the internet, except now I need to be able to grab information that exist in a few adjacent columns from the same row on this "LogDetails" worksheet. The code looks like the following right now:

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

'sSheetName = "2022"
sSheetName = ActiveSheet.Name

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.Value
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").Hyperlinks.Add Anchor:=Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="'" & sSheetName & "'!" & oldAddress, TextToDisplay:=oldAddress

Sheets("LogDetails").Columns("A:D").AutoFit
Application.EnableEvents = True
End If
End Sub

If I wanted to capture information from let's say a fixed column in the same row that is capturing the oldValue and Target.Value, for example using column H (8th column), how could I get it to grab that information for me?

Guessing it would start out something like:

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

Just don't know how to define that adjacent value in this Private Sub.


Thanks,

Steve

 

[offthelip]

I am not clear what you are trying to do:
oldvalue and oldaddress are not defined in your code, so where are these defined and how are they changed?? If you are trying to capture the value that was in a cell just before the user hits ENTER, you can't do it just by using the workhseet change event because the value in TARGET has already changed to the new value. The only way I have found to do this is to use the Workbook_SheetSelectionChange event to write into the the oldvalue variable. what are you expecting in the oldaddress variable, what are using it for??

 

[sspatriots]

Sorry, it might be easier if I put link here to where I found the code that I started with. Basically, this code will give me changes to a cell on a separate worksheet. It gives me the "old" and "new" values on any cell I change. I need to somehow get this code to also give me values in other columns that are adjacent to the cell I change, even though they don't change, just are in the same row.

Track Changes Automatically in Worksheet with VBA – Free Excel Training 2022

Regards,

Steve

 

[offthelip]

That website answers one of my questions because it includes the code:

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

which you need. This is a lesson to learn when asking for help it is useful to inlcude all the code ( unless it is huge)
however I still don't know what columns you want saved from the changed sheet to the log details??
aslo do you want the hyperlink back to the changed cell??

 

[sspatriots]

That website answers one of my questions because it includes the code:

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

which you need. This is a lesson to learn when asking for help it is useful to inlcude all the code ( unless it is huge)
however I still don't know what columns you want saved from the changed sheet to the log details??
aslo do you want the hyperlink back to the changed cell??

I'll work on a example of the output and the before worksheets and try and get it on here today. Thanks for looking into this. As you can tell, I only know enough to get by and piece meal stuff that I need. I have a very basic understanding of code and being away from it the last two years hasn't helped any. Selling houses.

 

[sspatriots]

Before:

Worksheet Tab: 2022


After: (Once I double-click in any cell on the worksheet, the LogDetails tab becomes unhidden and the information below shows up)

Worksheet Tab: 2022

Note: We have someone in our Receiving Dept that will enter the Date Received when something comes in and then changes that cell color in that first column to that standard style green that is available in the Home tab for “Good”. I have no idea why the first two columns have the same name. I’m sure I can change the names slightly. I just started work here on 02/28, so not trying to rock the boat to fast.

The history will continue to tack on to the next new row after each cell change.



LogDetails Tab:

Columns A through F are already what the code produces when I add the date to cell $L$16. Columns G through O represent the additional information in that same row 16 that I want to bring over with the change in that same row. Ideally, if I could have the cells the row we bring over have borders all around, but not really worried about that part right now.



Note: This worksheet has tabs for every year going back to 2008. I hope this makes better sense.

 

[offthelip]

your pictures are so small i can't read them , which columns on the Active sheett do you want copied to G to O on the logdetails sheet ( that is the only information I need I think)

 

[sspatriots]

Sorry, I missed one of your questions. If I could get the hyperlink to go with it, that would be great. I wasn't thinking that far ahead.

Also in the example above, cell values in the row that had the change incident I'm carrying the same row values in columns A, B, C, E, G, I, J, K & L to the new worksheet LogDetails. They become columns G through O, respectively.

 

[offthelip]

I have added a bit of code to copy columns A, B, C, E, G, I, J, K & L to columns G to O on the log details.
The hyperlink didn't work and I haven't got time to look at it. Having said that the code you found on the internet was not very good code for the task, it does multiple accesses to the worksheet every time it runs which is fairly slow , also it test for last row every time it writes to the worksheet i.e 6 times on every update. this is uneccessary because the lastrow hasn't changed. Note that my code writes 9 items to worksheet but does with with just 3 access to the worksheet

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName As String
'' add these defintiions
Dim temparr(1 To 1, 1 To 9) As Variant
'A, B, C, E, G, I, J, K & L  column numbers
colnos = Array(1, 2, 3, 5, 7, 9, 10, 11, 12)
'''''''''''''''''''''''''
'sSheetName = "2022"
sSheetName = ActiveSheet.Name

If ActiveSheet.Name <> "LogDetails" Then
Application.EnableEvents = False
'' this is not very good code, it could be much faster
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.Value
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").Hyperlinks.Add Anchor:=Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="'" & sSheetName & "'!" & oldAddress, TextToDisplay:=oldAddress
Sheets("LogDetails").Columns("A:D").AutoFit
'' add this bit.
rowno = Target.Row
inarr = Worksheets(sSheetName).Range(Cells(rowno, 1), Cells(rowno, 12))
For i = 1 To 9
 temparr(1, i) = inarr(1, colnos(i - 1))
Next i
With Sheets("LogDetails")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
 .Range(.Cells(LastRow, 7), .Cells(LastRow, 15)) = temparr
End With

''''
Application.EnableEvents = True
End If
End Sub

 

[sspatriots]

Thanks so much. That was perfect for that spreadsheet. I can ditch the hyperlinks...