Excel - Last Modified User in different sheets

[stellalol]

Hi,

I have been trying to make a cell show the last modified user.
I am using this code

Function getLastModifiedUser()
getLastModifiedUser = ActiveWorkbook.BuiltinDocumentProperties("Last Author")
End Funtion

Problem is, I have two sheets that have the same exact format as to the Last Motified User Cells
Could each sheet have a different Last Modified User name? Is that possible?

Thanks in advance guys!

 

[CephasOz]

Hi stellalol. Yes, each sheet can have a different value, using CustomDocumentProperties. Copy and paste the code below into your project.

You can set the value for the last user to have modified a particular sheet using the Worksheet_Change (see below).

You can then reference the value on a given sheet with the formula =GetVar("LU_"&MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255))

=== Copy and paste into each worksheet's code module ===

Private Sub Worksheet_Change(ByVal Target As Range)
    SetVar "LU_" & Me.Name, Env_UserName()
End Sub

=== Copy and paste into a normal code module ===

' Set a variable's value.
Function SetVar(ByVal strName As String, ByVal strValue As Variant) As Boolean
    Dim cTypeString As MsoDocProperties
    '
    SetVar = False
    On Error GoTo Err_Exit
    cTypeString = msoPropertyTypeString
    On Error GoTo New_Var
    ThisWorkbook.CustomDocumentProperties(strName).Value = strValue
    SetVar = True
Housekeeping:
    Exit Function
New_Var:
    Err.Clear
    On Error GoTo Err_Exit
    ThisWorkbook.CustomDocumentProperties.Add Name:=strName, LinkToContent:=False, Type:=cTypeString, Value:=strValue
    SetVar = True
    Resume Housekeeping
Err_Exit:
    Err.Clear
    Resume Housekeeping
End Function

' Get a variable's value.
Function GetVar(ByVal strName As String) As String
    GetVar = vbNullString
    On Error GoTo Err_Exit
    GetVar = ThisWorkbook.CustomDocumentProperties(strName).Value
Housekeeping:
    Exit Function
Err_Exit:
    Err.Clear
    Resume Housekeeping
End Function

' Environment value of UserName.
Function Env_UserName() As String
    Env_UserName = Environ("UserName")
End Function

 

[stellalol]

Hey CephasOz,

Thanks for the reply!

I've tried your code on a new workbook and I've done exactly what you've written.

Doe's the =GetVar("LU_"&MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255)) formula go on the Worksheet_Change? Cause I'm getting a Value error, is that because other sheets are blank maybe??

I'm a basic user in excel coding so I would be greatful if you could help me on this

 

[CephasOz]

Hi stellalol. No, the formula is the one thing that goes in a cell in the worksheet. That way, anyone opening the workbook will immediately be able to see who was the last user to modify each sheet.

 

[stellalol]

Thank you again CephasOz for the help. Unfortunatelly that doesn't help me.

So I splitted the sheets to two new excel files. Thing is that I want Last Modified User to show on every sheet as well as the date he/she modified anything.

I'm using the code I wrote above,

Function getLastModifiedUser()
getLastModifiedUser = ActiveWorkbook.BuiltinDocumentProperties("Last Author")
End Funtion

it works but only when I press enter on that specific cell, is there anyway it can change automatically when user exits?

Also I can't find for the Last Date modified code. Do you have any ideas?

You were a huge help. Thanks alot!

 

[CephasOz]

Hi stellalol. Because you want to store a different Last User for each worksheet, you won't be able to use ActiveWorkbook.BuiltinDocumentProperties("Last Author") as there is only one value for the whole workbook.

So you'll need to use the code that I gave you earlier. And if you want to store another variable for each worksheet, then it's just another line in the Worksheet_Change event. Like so:

Private Sub Worksheet_Change(ByVal Target As Range)
    SetVar "LU_" & Me.Name, Env_UserName()
    SetVar "LU_" & Me.Name & "_Date", Format(Date, "Short Date")
End Sub

Remember, that code has to be pasted into the code sheet for each worksheet. (Right-click the worksheet's tab, select View Code, and paste the code.)

Then, if you want one of those values to appear in a cell in a particular worksheet, just paste one of the formulas below into that cell. For Last User:
=GetVar("LU_"&MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255))
and for the date on which the worksheet was modified, paste the following formula into another cell:
=GetVar("LU_"&MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255)&"_Date")

 

[stellalol]

Hey CephasOz, thanks again for your response!

Just tried your code but I'm still getting a Value error in the cells!

Dunno if other codes come accross it an that's why it doesn't show.

So, I'll explain!

I've got a workbook with three sheets, a,b,c

a book contains all the data I need
b has lists for datavalidation
c is the new I created for Worksheet_Change Event

Code in a sheet has more an it's

Private Sub CommandButton1_Click()
Dim xAddress As String
xAddress = "1:1000"

If CommandButton1.Value Then
Application.ActiveSheet.Rows(xAddress).Hidden = True
CommandButton1.Caption = "Show Row"
Else
Application.ActiveSheet.Rows(xAddress).Hidden = False
CommandButton1.Caption = "Hide Row"

End If


End Sub

Private Sub HIDE_Click()
'Dim ws As Worksheet:    Set ws = ActiveSheet
Dim rCell As Range
Dim dValue As Date

Application.ScreenUpdating = False

'dValue = DateSerial(Day(Now), Month(Now), Year(Now))
    '.AutoFilterMode = False
For Each rCell In Range("H1:H" & Range("A" & Rows.Count).End(xlUp).Row)
    If rCell.Value <> "" Then
        If rCell.Value < DateSerial(Year(Now), Month(Now), Day(Now)) Then
            rCell.EntireRow.Hidden = True
        Else
            rCell.EntireRow.Hidden = False
        End If
    End If
Next rCell

Application.ScreenUpdating = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    SetVar "LU_" & Me.Name, Env_UserName()
End Sub

I put your code down at the end. Everything has breaks.

In b sheet

Only your code

Private Sub Worksheet_Change(ByVal Target As Range)

    SetVar "LU_" & Me.Name, Env_UserName()

End Sub

And in a normal module I insered

' Set a variable's value.
Function SetVar(ByVal strName As String, ByVal strValue As Variant) As Boolean
    Dim cTypeString As MsoDocProperties
    '
    SetVar = False
    On Error GoTo Err_Exit
    cTypeString = msoPropertyTypeString
    On Error GoTo New_Var
    ThisWorkbook.CustomDocumentProperties(strName).Value = strValue
    SetVar = True
Housekeeping:
    Exit Function
New_Var:
    Err.Clear
    On Error GoTo Err_Exit
    ThisWorkbook.CustomDocumentProperties.Add Name:=strName, LinkToContent:=False, Type:=cTypeString, Value:=strValue
    SetVar = True
    Resume Housekeeping
Err_Exit:
    Err.Clear
    Resume Housekeeping
End Function

' Get a variable's value.
Function GetVar(ByVal strName As String) As String
    GetVar = vbNullString
    On Error GoTo Err_Exit
    GetVar = ThisWorkbook.CustomDocumentProperties(strName).Value
Housekeeping:
    Exit Function
Err_Exit:
    Err.Clear
    Resume Housekeeping
End Function

' Environment value of UserName.
Function Env_UserName() As String
    Env_UserName = Environ("UserName")
End Function

Which you sugested.

In =GetVar("LU_"&MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255))
Filename is the name of the file and not the sheet right?
Where doe's $A$1 reffer to?
1,255 is the row range right can I make it bigger like for 1000?

 

[CephasOz]

If you type =CELL("filename",$A$1) into a cell in any saved worksheet, you will see the full filename and sheet name. The rest of the formula that I gave you is designed to extract from that full filename just the sheetname, so it can be combined to produce a variable name. For example, if the sheet name was "Accounts", the formula would work out to be the same as =GetVar("LU_Accounts"), and that's why it shows you the last user who updated that worksheet. So the 1,255 is not a range, it's part of the formula that extracts as many as 255 characters of the sheet name from the full filename. And the reference to $A$1 is given to choose a cell that is part of the worksheet for which the full filename is needed. It could be any cell, but it's convenient to use $A$1.

So, nothing should be changed. It works just as it is, regardless of where your data is, or how many rows or columns it has.

 

[stellalol]

@CephasOz It works Perfectly!!! Thank you very much for the explanation it helped alot!

 

[stellalol]

@CephasOz It works Perfectly!!! Thank you very much for the explanation it helped alot!

Date shows blank tho ?
Is there anything we forgot?