Worksheet_change Timestamp in Macro

[sassriverrat]

Hello- So I have three Macros that create sheets- Macro 1 creates "Noon" or Sheet1. Macro 2 creates "Noon#" which is essentially a copy of "Noon" and whatever number and can be near infinite. Macro 3 creates "Arrival" which is the summation of the info from all of the noons. Anyway, I wrote a timestamp piece a while ago that worked pretty well- I had to run EnableEvents before it would work which was odd and then disable again- not sure why, but othewise it worked. It was copied to each worksheet. Now with the macros creating new sheets on demand, I'd like a way to add it into the three macros. It's purpose it to create a timestamp that WON'T update when the workbook is reopened. Thanks for the help!

-Side note- when it was assigned to the individual sheets, I had a Sub ddd (application.EnableEvents) right before using the worksheet EACH time (even through it was in the workbook open sheet) to make my coding below work. It's elementary and maybe someone has something better.

Code Note- Yes it is designed that if something is entered into "R8" it puts a timestamp in F4. R8 is a data cell. If a date (a manual override) is put "W25", that date will showup to override the timestamp.

Private Sub Worksheet_Change(ByVal Target As Range)    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    If Not Intersect(Target, Range("R8,W25")) Is Nothing Then
        
        If Cells(8, 18) <> "" And Cells(25, 23) = "" Then
            Cells(4, 6) = Date
            Cells(4, 6).NumberFormat = "dd-mmm-yyy"
        End If


        If Cells(25, 23) <> "" Then
            Cells(4, 6) = Cells(25, 23).Value
            Cells(4, 6).NumberFormat = "dd-mmm-yyy"
        End If
        
          If Cells(8, 18) = "" And Cells(25, 23) = "" Then
            Cells(4, 6) = "No Data Input"
        End If


        If Cells(20, 26) = "'Yes" Then
            Cells(9, 18) = "'Exact"
        End If


        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With


    End If
End Sub

 

[Joe4]

I am a bit confused regarding what you are asking for here, but I can explain the "EnableEvents" part to you.
Basically, "EnableEvents" is what allows the automated Event Procedure VBA code (like "Worksheet_Change") to run automatically.
Sometimes, it is necessary to temporarily disable it, if the code itself is making changes to your sheet. Otherwise, it ends up calling itself, and depending on how it is written, you could get caught in an infinite loop!

So what you commonly see in Worksheet_Change code, is if it is making changes on the sheet, to temporarily set "EnableEvents" to False while the changes are made, and then changing it back to True afterwards. You just need to be careful, that there isn't any way that the code will be exited before re-enabling events, or else those events won't trigger until you do something to re-enable them (i.e. run code specifically to turn it back on or close/re-open Excel).

Hope that clarifies that for you.

Note, regarding your Worksheet_Change code, note that it is sheet specific. So if you have multiple sheets, you would need Worksheet_Change code for each sheet that you want to apply it to.

 

[Fluff]

Further to what Joe4 has said, you are turning "EnableEvents" off at the begin of your code, but only turning it back "On" if the changed cell is within the range R8:W25. So if you change A1 "EnableEvents" will NOT be turned back on.
What you need to do is put this

        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With

AFTER the "End If"

 

[sassriverrat]

Thank you for the explanation- just to be completely understanding- the application.enableEvents then only affects running Macros and not formulas within the cells, correct?

So the sheet- I can change these to true- but this form is supposed to put a timestamped date in the F4 cell if anything is input into R8 and it's supposed to put the contents from W25 into F4 (instead of the timestamp) if anything is put into W25. (W25 ordinarily stays blank). My issue is this-

My second macro is designed to copy the "noon" sheet, generating a whole new sheet with formulas and such. I'm trying to figure out how to put my timstamp into that macro code since it can't be a worksheet_change.....it needs to be embedded within another regular macro. Make sense?