log user who made a change to a cell

[jdhfch]

Hi,

Please can someone help?

I am looking to log in a cell the last user that made the change to a specific cell in a workbook shared on sharepoint.

for example, if someone put a Y in cell N5 called Jonesj, I would like the Jonesj to appear in cell O5?

Is this possible, please?

 

[saboh12617]

Hello, using the Change Event of the worksheet/workbook you want to keep an eye on, you can do something like this

I think you need to have the SharePoint Workbook avaliable/sync on your PC however. I do not know how to access it otherwise.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range) 
  Dim myRange As Range
  Set myRange = ThisWorkbook.Worksheets(1).Range("N5") 
  Dim sharePWb As Workbook
  Set sharePWb = ???
  If Not Intersect(Source, myRange) Is Nothing Then
    sharePWb.Worksheets(1).Range("O5").Value2 = Application.UserName
  End If
End Sub

 

[jdhfch]

Hello, using the Change Event of the worksheet/workbook you want to keep an eye on, you can do something like this

I think you need to have the SharePoint Workbook avaliable/sync on your PC however. I do not know how to access it otherwise.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
  Dim myRange As Range
  Set myRange = ThisWorkbook.Worksheets(1).Range("N5")
  Dim sharePWb As Workbook
  Set sharePWb = ???
  If Not Intersect(Source, myRange) Is Nothing Then
    sharePWb.Worksheets(1).Range("O5").Value2 = Application.UserName
  End If
End Sub

Thank you.

I am not sure if I am doing something wrong here. The workbook is held on my PC and is called Discounted Orders.xlsm - I have put this into the Set sharePWb = ??? par of your code, but it isn't working. I am also wanting it to not just be N5 & O5, it would be N6 and O6 etc as well as changes are made to them?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
Dim myRange As Range
Set myRange = ThisWorkbook.Worksheets(2).Range("N5")
Dim sharePWb As Workbook
Set sharePWb = "Discounted_orders.xlsm"
If Not Intersect(Source, myRange) Is Nothing Then
sharePWb.Worksheets(2).Range("O5").Value2 = Application.UserName
End If
End Sub

 

[saboh12617]

Hello again,

My apologies, the code was not very clear, and also incorect as it was not opening the file to write in it.

Please find below a correction.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
  Dim myRange As Range
  ' you enter here the range to be logged if modified
  Set myRange = ThisWorkbook.Worksheets(1).Range("N5:N6")
  Dim shareWbPath As String
  ' you enter here the local path to the synced file on your PC
  ' you need to put the full path
  shareWbPath = "C:\Users\XXX\OneDrive\Discounted_orders.xlsm"
  Application.ScreenUpdating = False
  If Not Intersect(Source, myRange) Is Nothing Then
    With Workbooks.Open(shareWbPath)
      ' if i understood corectly, you put the name of the user
      ' 2 columns to the right on the shared workbook (it's the offset part)
      .Worksheets(2).Range(Intersect(Source, myRange).Address).Offset(0, 2).Value2 = Application.UserName
      .Save
      .Close
    End With
  End If
  Application.ScreenUpdating = True
End Sub

However opening and closing the file at each "successful" modification can be quite heavy. Maybe it is better to send all modifications to the shared workbook once the current file is closed, or saved. But it is just my opinion. I mean it is already a tricky question when the Review > Show changes tab exists...

 

[jdhfch]

Hello again,

My apologies, the code was not very clear, and also incorect as it was not opening the file to write in it.

Please find below a correction.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
  Dim myRange As Range
  ' you enter here the range to be logged if modified
  Set myRange = ThisWorkbook.Worksheets(1).Range("N5:N6")
  Dim shareWbPath As String
  ' you enter here the local path to the synced file on your PC
  ' you need to put the full path
  shareWbPath = "C:\Users\XXX\OneDrive\Discounted_orders.xlsm"
  Application.ScreenUpdating = False
  If Not Intersect(Source, myRange) Is Nothing Then
    With Workbooks.Open(shareWbPath)
      ' if i understood corectly, you put the name of the user
      ' 2 columns to the right on the shared workbook (it's the offset part)
      .Worksheets(2).Range(Intersect(Source, myRange).Address).Offset(0, 2).Value2 = Application.UserName
      .Save
      .Close
    End With
  End If
  Application.ScreenUpdating = True
End Sub

However opening and closing the file at each "successful" modification can be quite heavy. Maybe it is better to send all modifications to the shared workbook once the current file is closed, or saved. But it is just my opinion. I mean it is already a tricky question when the Review > Show changes tab exists...

Thank you - worked perfectly

 

[saboh12617]

Glad to help, have a good day.