Add a Column Range to an AutoDate Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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 macroyou 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 said:
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.
Click to expand...
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 .
 
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
 
Thanks for you help
Kevin Brenner

Dave Peterson said:
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
Click to expand...
 
Back
Top