Change a worksheet_change to a workbook_change code

[sassriverrat]

I have a timestamp I've previously talked to people about. It's a worksheet_change item and I either need to make it into a macro that runs automatically (so when I input data into "R5" it puts a permanent, non-changing/updating timestamp "dd-mmm-yy" in "F4"

The fun part about this- it needs to add to sheets when the sheets are made- so I have a macro that creates a new sheet and formats the whole thing (talk about a long set of codes...). It also inserts all of the formulas into their respective cells. Now I need this timestamp to go in automatically when a new sheet is made and it can't just be a simple Now() as that would update each time the workbook is reopened.

Thanks!

 

[footoo]

Re: how to change a worksheet_change to a workbook_change code

(so when I input data into "R5" it puts a permanent, non-changing/updating timestamp "dd-mmm-yy" in "F4"

[F4]=Date

it needs to add to sheets when the sheets are made- so I have a macro that creates a new sheet and formats the whole thing (talk about a long set of codes...). It also inserts all of the formulas into their respective cells.

Can you not have a sheet template that has all the formatting and formulas, and make a copy of the template?

 

[sassriverrat]

Re: how to change a worksheet_change to a workbook_change code

[F4]=Date


Can you not have a sheet template that has all the formatting and formulas, and make a copy of the template?

That's actually exactly what I did- I wrote a very long macro that essentially creates the entire sheet, formatting, colors, columns/rows sizing, and fills in all of the cells with the formulas, titles, references, etc (mostly just using range.formulaR1C1) but the macro I had (see below) that gave me a timestamp was a worksheet_change item and so I don't know how I would apply it to the template. Won't the [F4] = Date update each time the sheet is used/the workbook updated? See how my macro works below- it isn't perfect- I seem to need to run application.enableEvents = True ahead of time to make it work but here it is:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)    With Sh
    
        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


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


        End If
    
End With
End Sub

There's probably a more efficient way to do this and I'm open to it!

 

[footoo]

Re: how to change a worksheet_change to a workbook_change code

Do macro that creates a new sheet (by copying the template) and which enters the date created in F4.

 

[sassriverrat]

Re: how to change a worksheet_change to a workbook_change code

which isn't bad except for one piece- if someone types into the Cells(25, 23) (W23 I believe), it needs to override that date (i.e. W23 = F4) AND second fun part- Cell C5 has a zone description in it because the computer runs on UTC and the sheet is printed in local time

 

[footoo]

Re: how to change a worksheet_change to a workbook_change code

Well which way do you want it?
Either F4 shows a permanent creation date (in which case remove the Worksheet_Change procedure), or overwrite F4 per the Worksheet_Change procedure.

Cell C5 has a zone description in it because the computer runs on UTC and the sheet is printed in local time

What problems arise from this?


If the same WS_Change procedure applies to all (or most) sheets, you could maybe change it to Workbook_SheetChange

 

[sassriverrat]

Re: how to change a worksheet_change to a workbook_change code

well when I was doing it with the formula it was an if statement, meaning =if(W25="", now(), W25)

Now I wasn't using a Now formula because I wanted it to freeze but you get the idea.
The F4 = date doesn't work because I don't have the zone description inputted into the sheet until it's already been made (i.e. the macro is finished) so it couldn't correct. This problem arises if local time is 11pm at night and the UTC time, for example, is 4am the next day, meaning the date is now a day ahead, technically speaking.

A Workbook_SheetChange would work if it's as simple as changing the header (I don't know enough- so I'm asking) but is there a way to make it exclude three sheets? And if it's applied at the workbook level, if a cell in one page is changed (i.e. W25 is changed on sheet1, will it try to override on the other sheets like sheet2 sheet 3, etc etc?

 

[footoo]

Re: how to change a worksheet_change to a workbook_change code

The F4 = date doesn't work because I don't have the zone description inputted into the sheet until it's already been made (i.e. the macro is finished) so it couldn't correct. This problem arises if local time is 11pm at night and the UTC time, for example, is 4am the next day, meaning the date is now a day ahead, technically speaking.

So isn't it the same problem for the dates entered by the WS_Change?

Try this procedure. Put it in the ThisWorkbook module :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name = "A" _
    Or ActiveSheet.Name = "B" _
    Or ActiveSheet.Name = "C" _
    Then Exit Sub 'change sheet names as equired
With Application
    .EnableEvents = False
    .ScreenUpdating = False
If Not Intersect(Target, Range("R8,W25")) Is Nothing Then
    If Cells(8, 18) <> "" And Cells(25, 23) = "" Then
        Cells(4, 6) = "No Data Input"
    ElseIf Cells(25, 23) <> "" Then
        Cells(4, 6) = Cells(25, 23).Value
        Cells(4, 6).NumberFormat = "dd-mmm-yyy"
    End If
End If
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

 

[sassriverrat]

Re: how to change a worksheet_change to a workbook_change code

so you're coding looks great. I swapped the letters out for the sheets to NOT have this applicable to. I've also inserted this into the ThisWorkbook Module.

It all makes sense to me what it's doing, however, it isn't working. I've been looking and I'm not exactly sure why. I changed the .enableevents to True just in case but that didn't work.

Is there a way to have a macro that automatically could add this to the individual sheet objects?

 

[sassriverrat]

Re: how to change a worksheet_change to a workbook_change code

Well- don't know what happened other than restarting my machine (maybe the ram needed to be cleared for some odd reason) but it's working! Thank you!