Macro Stoped Working

[MikeCopeland]

Several years ago I received the macro below (from Ron Rosenfeld)
that I use to populate an Excel spreadsheet for my annual taxes and
expenses. The instructions and macro have always worked as needed, but
today, after installing the code it worked - until I accidently keyed
garbage into a cell in the column.
After removing the bad data, the subprogram doesn't work. I've tried
to repeat all steps in the process, but anything I try fails to
reactivate the logic. Please advise. TIA
Note: I'm now using Office 360 on a network.

[Quoted response from Ron Rosenfeld]:
"To enter this event-triggered Macro, right click on the sheet tab.
Select "View Code" from the right-click drop-down menu. Then paste the
code below into the window that opens.
Be sure to set "rg" to the range where you want this to occur."
Will only subtract a year if the year entered is 2011 (or converted
to 2011 by Excel):"
============================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
Set rg = Range("B:B") 'date entry column
If Not Intersect(Target, rg) Is Nothing Then
Application.EnableEvents = False
If Target.Count = 1 And IsDate(Target) And _
Year(Target) = Year(Date) Then
Target.Value = DateAdd("yyyy", -1, Target)
End If
End If
Application.EnableEvents = True
End Sub

 

[GS]

This is a typical result where code doesn't impliment any error
handling and so if an error occured then Application.EnableEvents =
False. Type in the Immediate Window of the VBE...

Application.EnableEvents=True

...and hit 'Enter' to reset this. Then modify the code as follows...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range

Set rg = Range("B:B") 'date entry column
If Not Intersect(Target, rg) Is Nothing Then
On Error GoTo ErrExit
Application.EnableEvents = False
If Target.Count = 1 And IsDate(Target) And _
Year(Target) = Year(Date) Then
Target.value = DateAdd("yyyy", -1, Target)
End If
End If

ErrExit:
Application.EnableEvents = True
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

There's an income tax calculator Ron & I worked on this past fall that
you can find here...

https://app.box.com/s/23yqum8auvzx17h04u4f

Look for "TaxCalculator_IRS.xls"...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion