Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheetchange date and time
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheetchange date and time
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheetchange date and time
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheetchange date and time
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. "Jeff" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting text format of time/date into Excel time/date for subtr | Excel Worksheet Functions | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) |