Saving macro
Posted by Greg on March 12, 2001 7:27 AM
I have the following code underlying a sheet:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 32 Then
ThisRow = Target.Row
If Target <> "" And Target >= 0 Then
With Worksheets("Data Entry").Range("AG1")
.Value = Range("AG1").Value + 1
Worksheets("Graph Data").Rows(Target.Row).Hidden = False
End With
Else
With Worksheets("Data Entry").Range("AG1")
.Value = Range("AG1").Value - 1
Worksheets("Graph Data").Rows(Target.Row).Hidden = True
End With
End If
End If
ActiveWorkbook.Save
End Sub
Whenever this macro gets run, it saves twice. Could
somebody tell me why and let me know how to get it to
only save once?
Posted by Dave Hawley on March 13, 2001 1:12 AM
ActiveWorkbook.Save
Hi Greg
Your code is changing cell contents and thus causing the Event to fire again. You can stop this by using the code below:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ResetEvents
If Target.Column = 32 Then
ThisRow = Target.Row
If Target <> "" And Target >= 0 Then
With Worksheets("Data Entry").Range("AG1")
Application.EnableEvents = False
.Value = Range("AG1").Value + 1
Worksheets("Graph Data").Rows(Target.Row).Hidden = False
End With
Else
With Worksheets("Data Entry").Range("AG1")
.Value = Range("AG1").Value - 1
Worksheets("Graph Data").Rows(Target.Row).Hidden = True
End With
End If
End If
ActiveWorkbook.Save
ResetEvents:
Application.EnableEvents = True
End Sub
Dave
OzGrid Business Applications
