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!

 

[sassriverrat]

Re: how to change a worksheet_change to a workbook_change code

Now one problem- every sheet has the same date. Basically every day a new sheet is used so each sheet will have a different date in that F4 box....

 

[footoo]

Re: how to change a worksheet_change to a workbook_change code

The code I posted was wrong. Should be :

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) = Date
            Cells(4, 6).NumberFormat = "dd-mmm-yyy"
        ElseIf Cells(25, 23) <> "" Then
            Cells(4, 6) = Cells(25, 23).Value
            Cells(4, 6).NumberFormat = "dd-mmm-yyy"
        ElseIf Cells(8, 18) = "" And Cells(25, 23) = "" Then
            Cells(4, 6) = "No Data Input"
        End If
        .EnableEvents = True
        .ScreenUpdating = True
    End If
End With
End Sub

 

[sassriverrat]

Re: how to change a worksheet_change to a workbook_change code

So that’s basically what I had but it updates every page and locks the date in, but I want each page to have its own date when the cell is changed. They should not be all the same.

 

[sassriverrat]

Re: how to change a worksheet_change to a workbook_change code

You sir, are amazing, I didn’t notice the movement of that end if till the end!

 

[footoo]

Re: how to change a worksheet_change to a workbook_change code

.

 

[sassriverrat]

Re: how to change a worksheet_change to a workbook_change code

Ok the fun continues-

I haven't changed anything in the past few days, but when I open the workbook with all sheets closed that this Workbook_SheetChange applies to, and then add a new sheet, cell F4 (the one that should be affected) is blank and when I enter data into either of the two activation cells it stays blank.

Update-
In my sheet adder (that creates the sheet and formats it), I ended the sheet with an new part- Application.EnableEvents = True Everything SEEMS to be working again but not sure why I had to add this.

 

[footoo]

Re: how to change a worksheet_change to a workbook_change code

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) = Date
            Cells(4, 6).NumberFormat = "dd-mmm-yyy"
        ElseIf Cells(25, 23) <> "" Then
            Cells(4, 6) = Cells(25, 23).Value
            Cells(4, 6).NumberFormat = "dd-mmm-yyy"
        ElseIf Cells(8, 18) = "" And Cells(25, 23) = "" Then
            Cells(4, 6) = "No Data Input"
        End If
[COLOR=#ff0000]    End If[/COLOR]
[COLOR=#ff0000]    .EnableEvents = True[/COLOR]
[COLOR=#ff0000]    .ScreenUpdating = True[/COLOR]
End With
End Sub

 

[sassriverrat]

You’re saying delete these three things?

 

[footoo]

You’re saying delete these three things?

No.
Amendment highlighted.

 

[sassriverrat]

Noted. Seems to be working. Thank you. Now why would that change make the difference? And it had the same effect as me putting an enableevents = true at the end of my sheet creator