Change Username and Time Only When a Change is Made using VBA

RandyD123

Active Member
Joined
Dec 4, 2013
Messages
289
Office Version
  1. 2016
Platform
  1. Windows
What I would like my sheet to do is in cell M2 have the users name show and in cell M3 have the current time show. But I only want these cells to change IF the user enters or changes any other cell in the document. That range is B5 - M14. So if anyone changes one of those cells, then M2 and M3 only change at that point. I am using the code below which works when the workbook is opened but others need to open the workbook just to view it and I don't want M2 or M3 to change for people that will only view the document.

VBA Code:
Private Sub Workbook_Open()
 Dim Ws As Worksheet
 Set Ws = ActiveSheet
 Ws.Range("M2") = Environ("Username")
 Ws.Range("M3") = Now
 End Sub

I had set it to work on "save only" but when someone embeds the file in an e-mail they technically don't have to save the document and in that case M2 and M3 won't change.

Thank you for any help on this.
 
In post #3 you said:

that is different from what you stated in post #7:

so we don't need an event 'Workbook_BeforeSave' but need once again the 'Workbook_SheetChange'.
Let's see if I correctly understood your request but now I'm assuming that you have no other sheets than "Travel Orders (sheet 1 of 2)" and "data" in your workbook. Leave only this macro in ThisWorkbook pane:
VBA Code:
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Ws     As Worksheet
    Set Ws = ActiveSheet
    Application.EnableEvents = False
    If Ws.Name <> "Data" Then
        If Not Intersect(Target, Range("B5:M14")) Is Nothing Then
            Ws.Range("M2") = Environ("Username")
            Ws.Range("M3") = Now
        End If
    Else
        Ws.Range("E10") = Environ("Username")
        Ws.Range("E11") = Now
    End If
    Application.EnableEvents = True
End Sub
That code doesn't change the Data sheet when I'm on the Data sheet. My code works, but it looks messy. But now at least both sheets work independently of each other, as desired. Sorry for the confusion but I think in post 3 I stated that "So I need these sheets to work independently of each other". Anyway, as I said my code looks messy but it works perfectly, just don't know if it can be cleaned up. Thank you for all the time you have spent on this so far!
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
In post #3 you said:

that is different from what you stated in post #7:

so we don't need an event 'Workbook_BeforeSave' but need once again the 'Workbook_SheetChange'.
Let's see if I correctly understood your request but now I'm assuming that you have no other sheets than "Travel Orders (sheet 1 of 2)" and "data" in your workbook. Leave only this macro in ThisWorkbook pane:
VBA Code:
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Ws     As Worksheet
    Set Ws = ActiveSheet
    Application.EnableEvents = False
    If Ws.Name <> "Data" Then
        If Not Intersect(Target, Range("B5:M14")) Is Nothing Then
            Ws.Range("M2") = Environ("Username")
            Ws.Range("M3") = Now
        End If
    Else
        Ws.Range("E10") = Environ("Username")
        Ws.Range("E11") = Now
    End If
    Application.EnableEvents = True
End Sub
What I mean by my code, I really mean mostly all of your code. I think the above code needed something after the else statement for it to work. Thank You
 
Last edited:
Upvote 0
Sorry, now I'm lost. There is no need to do something when you open the workbook so why are you using event Workbook_Open ?
For sheet "Travel Orders", do you need it to be updated on every change in itself (Range B5:M14) with user-time-stamp in M2-M3 ?
For hidden sheet "Data", do you need it to be updated on every change in itself (entire sheet) with user-time-stamp in E10-E11 and also in M2-M3 if a change has been made (Range B5:M14) in sheet "Travel Orders" ?
 
Upvote 0
Sorry, now I'm lost. There is no need to do something when you open the workbook so why are you using event Workbook_Open ?
For sheet "Travel Orders", do you need it to be updated on every change in itself (Range B5:M14) with user-time-stamp in M2-M3 ?
For hidden sheet "Data", do you need it to be updated on every change in itself (entire sheet) with user-time-stamp in E10-E11 and also in M2-M3 if a change has been made (Range B5:M14) in sheet "Travel Orders" ?
I'll try to explain. I work in an office with 6 other persons. Any one of us can created "Travel Orders" for a person that is going to travel. Those orders get submitted to a Supervisor for approval. So what has to happen is for the person creating the orders, is, there name and time need to automatically go into M2 and M3, but only when they actually use a drop down in any of the fields (B5:M14). The file is read only but if M2 and M3 didn't change automatically, when a drop down is used, they could potentially make changes to the document without M2 or M3 updating. They could then embed the document into an e-mail and it would look like someone else created the document. M2 and M3 are locked and password protected. Then the document gets submitted for approval, which means that M2 and M3 CANNOT change "on open", but ONLY when a change is made.

The hidden sheet "Data" contains all the info that goes into the drop downs via Data Validation. The only time that tab would get changed is if we add new employees that may travel. But I need to know when someone changes that tab and adds or deletes any info. As I said these tabs need to work separately from each other.

All is working PERFECTLY this code, just was looking to clean up this code. You can see that on the Travel Orders sheet there is "Application Events" but on the code for the Data Sheet there is not. Not sure if that matters. Hope this clears things up. Thank You!!!

VBA Code:
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Ws As Worksheet
    Set Ws = ActiveSheet
    If Ws.Name = "Data" Then Exit Sub
    If Not Intersect(Target, Range("B5:M14")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Travel Orders").Unprotect Password:="password"
        Ws.Range("M2") = Environ("Username")
        Ws.Range("M3") = Now
        Sheets("Travel Orders").Protect Password:="password"
        Application.EnableEvents = True
    End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 Dim Ws As Worksheet
 Set Ws = ActiveSheet
     If Ws.Name = "Travel Orders" Then Exit Sub
     Sheets("Data").Unprotect Password:="password"
     Ws.Range("E10") = Environ("Username")
     Ws.Range("E11") = Now
     Sheets("Data").Protect Password:="password"
 End Sub
 

Attachments

  • Capture.JPG
    Capture.JPG
    139.4 KB · Views: 11
Upvote 0
Got it.
You will need "Application Events" in event BeforeSave macro elsewise you will get redundant triggering of event "Workbook_SheetChange" when changes in cells E10-E11 occur.
Try now in debugging mode, move to sheet "Data" then goto to event BeforeSave and lauch it with key F8 and keep going with F8.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top