On Mar 26, 12:07*pm, Jeff wrote:
On Mar 26, 9:18*am, JLGWhiz wrote:





You need a little more code added:

For Each Sh In ThisWorkbook.Sheets
If Sh.Type = -4167 Then
* * Application.EnableEvents = False
* * * * If ActiveSheet.Name = "Electrical status" Then
* * * * Sh.Range("B1:C1").Value = Date & " *" & Time
* * * * End If
* * * * If ActiveSheet.Name = "Unit Status" Then
* * * * Sh.Range("A3:C3").Value = Date & " *" & Time
* * * * End If
* * Application.EnableEvents = True
End If
Next
End Sub

You just needed to move the focus from one sheet to another so it can check
to see what the names are. *It can't do that while focus is on just one sheet.

"Jeff" wrote:
I got an example of this code from another discussion.

If Sh.Type = -4167 Then
* * Application.EnableEvents = False
* * * * * * * * Sh.Range("B1:C1").Value = Date & " *" & Time
* * Application.EnableEvents = True

End If

End Sub

This code works great. *I am trying to build upon this to designate
where to put the date and time on specific sheets. *The code I am
trying to use is this.

If Sh.Type = -4167 Then
* * Application.EnableEvents = False
* * * * If ActiveSheet.Name = "Electrical status" Then
* * * * Sh.Range("B1:C1").Value = Date & " *" & Time
* * * * End If
* * * * If ActiveSheet.Name = "Unit Status" Then
* * * * Sh.Range("A3:C3").Value = Date & " *" & Time
* * * * End If
* * Application.EnableEvents = True

End If

End Sub

This code doesn't work. *I am guessing that I am not referencing the
sheet correctly when I test to see which sheet the change is occurring
on. *I am a novice in excel and I don't really have the syntax down
for how to reference objects. *I don't understand the sh.type =
-4167. *Can anyone explain what this means?

Any help would be appreciated. *This is very useful for tracking the
date and time of changes on an indivdual sheet.

TIA

Jeff- Hide quoted text -

- Show quoted text -

Thanks for the help. *The code at least processes now. *It isn't
working quite as I would like it to.

The extra code helped some, but seems to make each sheet become the
activesheet and then changes the date. *I only want the date to change
the sheet that is being edited not on all sheets. *The sheets aren't
all layed out the same so the date will be in a different area on each
sheet and I only want the date to update when a change is made on a
sheet.

This is probably something very simple that I am missing.- Hide quoted text -

- Show quoted text -


This is the full text of the code of the original code I forgot to
include th sheetchange event.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Sh.Type = -4167 Then
Application.EnableEvents = False
Sh.Range("B1:C1").Value = Date & " " & Time
Application.EnableEvents = True
End If
End Sub