sheetchange date and time
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.
|