MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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