sheetchange date and time

[Jeff]

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

 

[JLGWhiz]

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]

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.














- 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.

 

[Jeff]

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

 

[JLGWhiz]

The worksheet change event will trigger the code in that specific worksheet.
For the code to apply to other sheets, the code must specifically state the
sheet, the range and the action to be taken. When you used the statement "If
ActiveSheet.Name = "Unit Status" Then", it does nothing because you have not
shifted focus to that sheet, so the condition will always be False. In other
words the ActiveSheet is the sheet the Worksheet_Change event is in. When
using the For Each sh In ActiveWorkbook.Sheets statement, the code tells VBA
to check each sheet and if the name matches, then do something. If the name
does not match, it should do nothing.

For more explanation, see "Looping Through a Range of Cells" in VBA help
files.