Date sheet last modified

I'd like to automatically record a date stamp

Where do you want the date stamp to be recorded ??

In the sheets'code put

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Address = Range("F3").Address) Then Exit Sub
    Application.EnableEvents = False
    Range("F3") = Format(Date, "mmmm dd yyyy")
    Application.EnableEvents = True
End Sub

Originally Posted by PCI

In the sheets'code put

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Address = Range("F3").Address) Then Exit Sub
    Application.EnableEvents = False
    Range("F3") = Format(Date, "mmmm dd yyyy")
    Application.EnableEvents = True
End Sub

hmm... It doesn't seem to do anything for me. I'll try playing with it a bit later on to see if I can get it to work
Update: Ignore that. Got it working and its running perfect. Thank you! For some reason all VBA had stopped executing when I tried it the first time.

For some reason all VBA had stopped executing when I tried it the first time.

A reason could be that you do not exit properly from the macro and did not finish it and did not restaure events
Next statement not done

    Application.EnableEvents = True

To solve it:
Restart Excel
or
Use next sub in a module

 Sub Rescue
      Application.EnableEvents = True
 end sub

Originally Posted by PCI

A reason could be that you do not exit properly from the macro and did not finish it and did not restaure events
Next statement not done

Yeah, I keep having issues with VBA not running. I think my issue might be that I'm not combining it with the following code properly:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("A4")
If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub

What is the best way for me to combine these?

Originally Posted by PCI

A reason could be that you do not exit properly from the macro and did not finish it and did not restaure events
Next statement not done

    Application.EnableEvents = True

To solve it...
Restart Excel

Or, simply type

Application.EnableEvents = True

in the immediate window

Tim

number9, do not forget code tags when posting code .. see forum's rules ...!
Try next code else post an Excel sample

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If (Target.Address = Range("A4").Address) Then _
        If Target <> "" Then Application.ActiveSheet.Name = VBA.Left(Target, 31)

    If (Target.Address = Range("F3").Address) Then Exit Sub
        Application.EnableEvents = False
        Range("F3") = Format(Date, "mmmm dd yyyy")
        Application.EnableEvents = True
End Sub

What do you want to do with next code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("A4")
If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub

See file attached

I think it's not a good idea to set the "Target" manually. Best to leave that alone, and define another range instead. Note I'd run this on a change, not a selection change otherwise it'll be running willy-nilly all the time even if the actual data on the sheet haven't changed.

It is also debatable whether it's a good idea to run this on every change. Won't make a difference if it's only run every so often, but aside from the load there are side effects. You are aware, I assume, that each time VBA runs on such an event it resets the "undo" feature in Excel, for example. Would it not be more circumspect to trigger the update if one or more specific cells are changed? Anyhow...

How about this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngCounter As Range
    Dim rngDate As Range
    Set rngCounter = Me.Range("B9")
    Set rngDate = Me.Range("F3")

    Application.EnableEvents = False
    
    rngDate.Value = Date   
    Me.Name = rngCounter.Value & " - " & Format(rngDate.Value, "mmmm dd yyyy")

    Application.EnableEvents = True

End Sub

Tim

To narrow it down to specific cells, you can use the "intersect" function. That checks to see if two or more ranges have cells in common.

"Target" is the range (here: cell) that's just been changed, so we intersect "Target" with the range(s) we want the code to run from. If the two have cells in common we know to run the code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngCounter As Range
    Dim rngDate As Range
    Dim rngToTest As Range
    
    On Error GoTo error_handler                     ' if there's an error, exit gracefully
    Application.EnableEvents = False                'switch off events so we can't end up with an infinite loop
    
    ' using range variables to make code more readable, but not really necessary
    Set rngCounter = Me.Range("B9")             ' the range that holds the counter
    Set rngDate = Me.Range("F3")                ' range for change date
    
    If Not Intersect(Target, Union(Range("B9"), Range("TotalCharged"))) Is Nothing Then   ' if it's not "nothing" we want to perform some action
        
        rngDate.Value = Date
        Me.Name = CStr(rngCounter.Value) & " - " & Format(rngDate.Value, "mmmm dd yyyy")    'rename the worksheet
        
    End If

' more similar "if... then" clauses can be added here to trigger actions on other cells if desired

error_handler:
    Select Case Err.Number
        Case 0      ' no error
        Case Else:  MsgBox (Err.Description)
    End Select

    ' events back on again now we're finished; we want this run even if there was an error
    Application.EnableEvents = True

End Sub

I tried to build in at least some explanation with the comments. Does that make sense to you?

Tim


NB: Forgot to say, I've bloated that a bit for explanation purposes. In practice, I'd probably just use this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False                'switch off events so we can't end up with an infinite loop
    
    If Not Intersect(Target, Union(Range("B9"), Range("TotalCharged"))) Is Nothing Then   ' if it's not "nothing" we want to perform some action
        Range("F3").Value = Date
        Me.Name = CStr(Range("B9").Value) & " - " & Format(rngDate.Value, "mmmm dd yyyy")    'rename the worksheet
    End If
    
    Application.EnableEvents = True

End Sub

I suspect you're probably right about that - the cell's been recalculated but it hasn't changed, the worksheet_change event only fires when the cell's changed manually.

I think you'd have to modify the code so it fires on cells where the user inputs their data, instead of where your formulae are.

Tim

Originally Posted by harrisonland

I think you'd have to modify the code so it fires on cells where the user inputs their data, instead of where your formulae are.

Hmm... that's going to be a problem. The idea is to get the entire sheet automated.
(and although the sheet/book data is entered onto is technically macro-enabled, Data will be entered on a mobile device through a version of excel that doesn't currently support VBA)

Maybe I could create a button and link it to that...

I've turned it into a selection change for an easy way to make it work for the time being.
You mentioned above that you wouldn't use a selection change. Is there other downsides to this other than the undo option turned off and slower speed?

Sorry for late reply - been away for a bit.

I wouldn't use selection change because the event will fire every time a user moves from one cell to the next. It would fire if the target cell were selected, irrespective of whether the value there had changed or not.

If a user opens the worksheet, pokes around then closes it again, the event could fire and update the date stamp... if that doesn't both you then it's fine, but it wasn't what you asked for in the description!

Hope this was all of some help, anyway.

Tim

PS - Please don't forget to mark teh question as SOLVED using the thread tools at the top of the page!

PPS - thanks for rep!