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

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

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

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

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