Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a Column Range to an AutoDate Macro
I have this macro in the Workbook:
''This set of Code makes the AutoDate automatically If Not IsEmpty(Cells(Target.Row, 3)) Then Exit Sub If Not IsEmpty(Target.Value) Then Cells(Target.Row, 3) = Format(Now(), "mm-dd-yy") But I'm having trouble with another macro, appears to be in conflict with this macro: Sub MySum() Range("I1") = Application.WorksheetFunction.Sum(Selection) Cancel = True End Sub What I like to do is for the first macro to run only when data is entered in a the Range C:G. Can someone help me with this I cant get the proper syntax , I'm sure it can be done but I cant . Thank you very much for your help! Kevin Brenner |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a Column Range to an AutoDate Macro
How about:
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Not IsEmpty(Cells(Target.Row, 3)) Then Exit Sub If Intersect(Target, Me.Range("c:g")) Is Nothing Then Exit Sub On Error GoTo errHandler: If Not IsEmpty(Target.Value) Then Application.EnableEvents = False Me.Cells(Target.Row, 3) = Format(Now(), "mm-dd-yy") End If errHandler: Application.EnableEvents = True End Sub But you could have taken the opposite approach and told excel to stop looking for changes in your MySum function: Sub MySum() application.enableevents = false Range("I1") = Application.WorksheetFunction.Sum(Selection) application.enableevents = true 'Cancel = True End Sub I'm not sure why the cancel is in your code. Kevin B wrote: I have this macro in the Workbook: ''This set of Code makes the AutoDate automatically If Not IsEmpty(Cells(Target.Row, 3)) Then Exit Sub If Not IsEmpty(Target.Value) Then Cells(Target.Row, 3) = Format(Now(), "mm-dd-yy") But I'm having trouble with another macro, appears to be in conflict with this macro: Sub MySum() Range("I1") = Application.WorksheetFunction.Sum(Selection) Cancel = True End Sub What I like to do is for the first macro to run only when data is entered in a the Range C:G. Can someone help me with this I cant get the proper syntax , I'm sure it can be done but I cant . Thank you very much for your help! Kevin Brenner -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a Column Range to an AutoDate Macro
Thank you very much,
I had all my ideas in the wrong place, I was trying to add a section like: If Not IsEmpty(Cells(Target.Range("C:G")) Then Exit Sub and apparently this is the wrong tweak, I did not know I had to use the Intersect command. You also said " I'm not sure why the cancel is in your code." on macro Sub MySum() Range("I1") = Application.WorksheetFunction.Sum(Selection) Cancel = True End Sub you are correct that was some left over code from a recorded macro and I forget to delete that line out. Thank you again. Kevin Brenner "Dave Peterson" wrote in message ... How about: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Not IsEmpty(Cells(Target.Row, 3)) Then Exit Sub If Intersect(Target, Me.Range("c:g")) Is Nothing Then Exit Sub On Error GoTo errHandler: If Not IsEmpty(Target.Value) Then Application.EnableEvents = False Me.Cells(Target.Row, 3) = Format(Now(), "mm-dd-yy") End If errHandler: Application.EnableEvents = True End Sub But you could have taken the opposite approach and told excel to stop looking for changes in your MySum function: Sub MySum() application.enableevents = false Range("I1") = Application.WorksheetFunction.Sum(Selection) application.enableevents = true 'Cancel = True End Sub I'm not sure why the cancel is in your code. Kevin B wrote: I have this macro in the Workbook: ''This set of Code makes the AutoDate automatically If Not IsEmpty(Cells(Target.Row, 3)) Then Exit Sub If Not IsEmpty(Target.Value) Then Cells(Target.Row, 3) = Format(Now(), "mm-dd-yy") But I'm having trouble with another macro, appears to be in conflict with this macro: Sub MySum() Range("I1") = Application.WorksheetFunction.Sum(Selection) Cancel = True End Sub What I like to do is for the first macro to run only when data is entered in a the Range C:G. Can someone help me with this I cant get the proper syntax , I'm sure it can be done but I cant . Thank you very much for your help! Kevin Brenner -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a Column Range to an AutoDate Macro
And watch out. Isempty likes to look at just one cell.
If you need to inspect a multicell range, you could use: if application.counta(range("C:G")) 0 then msgbox "at least one cell is non-empty else msgbox "all empty" end if Kevin wrote: Thank you very much, I had all my ideas in the wrong place, I was trying to add a section like: If Not IsEmpty(Cells(Target.Range("C:G")) Then Exit Sub and apparently this is the wrong tweak, I did not know I had to use the Intersect command. You also said " I'm not sure why the cancel is in your code." on macro Sub MySum() Range("I1") = Application.WorksheetFunction.Sum(Selection) Cancel = True End Sub you are correct that was some left over code from a recorded macro and I forget to delete that line out. Thank you again. Kevin Brenner "Dave Peterson" wrote in message ... How about: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Not IsEmpty(Cells(Target.Row, 3)) Then Exit Sub If Intersect(Target, Me.Range("c:g")) Is Nothing Then Exit Sub On Error GoTo errHandler: If Not IsEmpty(Target.Value) Then Application.EnableEvents = False Me.Cells(Target.Row, 3) = Format(Now(), "mm-dd-yy") End If errHandler: Application.EnableEvents = True End Sub But you could have taken the opposite approach and told excel to stop looking for changes in your MySum function: Sub MySum() application.enableevents = false Range("I1") = Application.WorksheetFunction.Sum(Selection) application.enableevents = true 'Cancel = True End Sub I'm not sure why the cancel is in your code. Kevin B wrote: I have this macro in the Workbook: ''This set of Code makes the AutoDate automatically If Not IsEmpty(Cells(Target.Row, 3)) Then Exit Sub If Not IsEmpty(Target.Value) Then Cells(Target.Row, 3) = Format(Now(), "mm-dd-yy") But I'm having trouble with another macro, appears to be in conflict with this macro: Sub MySum() Range("I1") = Application.WorksheetFunction.Sum(Selection) Cancel = True End Sub What I like to do is for the first macro to run only when data is entered in a the Range C:G. Can someone help me with this I cant get the proper syntax , I'm sure it can be done but I cant . Thank you very much for your help! Kevin Brenner -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a Column Range to an AutoDate Macro
Thanks for you help
Kevin Brenner "Dave Peterson" wrote in message ... And watch out. Isempty likes to look at just one cell. If you need to inspect a multicell range, you could use: if application.counta(range("C:G")) 0 then msgbox "at least one cell is non-empty else msgbox "all empty" end if Kevin wrote: Thank you very much, I had all my ideas in the wrong place, I was trying to add a section like: If Not IsEmpty(Cells(Target.Range("C:G")) Then Exit Sub and apparently this is the wrong tweak, I did not know I had to use the Intersect command. You also said " I'm not sure why the cancel is in your code." on macro Sub MySum() Range("I1") = Application.WorksheetFunction.Sum(Selection) Cancel = True End Sub you are correct that was some left over code from a recorded macro and I forget to delete that line out. Thank you again. Kevin Brenner "Dave Peterson" wrote in message ... How about: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Not IsEmpty(Cells(Target.Row, 3)) Then Exit Sub If Intersect(Target, Me.Range("c:g")) Is Nothing Then Exit Sub On Error GoTo errHandler: If Not IsEmpty(Target.Value) Then Application.EnableEvents = False Me.Cells(Target.Row, 3) = Format(Now(), "mm-dd-yy") End If errHandler: Application.EnableEvents = True End Sub But you could have taken the opposite approach and told excel to stop looking for changes in your MySum function: Sub MySum() application.enableevents = false Range("I1") = Application.WorksheetFunction.Sum(Selection) application.enableevents = true 'Cancel = True End Sub I'm not sure why the cancel is in your code. Kevin B wrote: I have this macro in the Workbook: ''This set of Code makes the AutoDate automatically If Not IsEmpty(Cells(Target.Row, 3)) Then Exit Sub If Not IsEmpty(Target.Value) Then Cells(Target.Row, 3) = Format(Now(), "mm-dd-yy") But I'm having trouble with another macro, appears to be in conflict with this macro: Sub MySum() Range("I1") = Application.WorksheetFunction.Sum(Selection) Cancel = True End Sub What I like to do is for the first macro to run only when data is entered in a the Range C:G. Can someone help me with this I cant get the proper syntax , I'm sure it can be done but I cant . Thank you very much for your help! Kevin Brenner -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autodate and total hours | Excel Worksheet Functions | |||
what did I do to get autodate? | Excel Discussion (Misc queries) | |||
Autodate worksheet tab | Excel Discussion (Misc queries) | |||
How do I autodate(excel) every 7 days ex. 1/1/06-1/31/06? | New Users to Excel | |||
autodate and datetime stamp | Excel Worksheet Functions |