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

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

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

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

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