Help with Error Handling: Worksheet_Change event

[JumboCactuar]

Hi,
i have the following sheet code:

Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo myerror


With Application
  .EnableEvents = False
  .ScreenUpdating = False
  
If Target.Cells.Count = 1 Then
    If Left(Target.Address, 3) = "$F$" And Target <> "" Then
    Range("H" & Target.Row).Value = Now
    
    ThisWorkbook.Save


    End If
    
End If
    
  .EnableEvents = True
  .ScreenUpdating = True
End With


myerror:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub




End Sub

Problem i have is when the file fails to save (sometimes happens on network)
everytime i enter into column F the workbook gets very slow at calculating

Im wondering if i havent handled this error properly? Or is there anything i can change

 

[NdNoviceHlp]

I hate worksheet change events... they seem recursive. U can trial this. HTH. Dave

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Flag As Boolean
On Error GoTo myerror
If Not Flag Then
With Application
  .EnableEvents = False
  .ScreenUpdating = False
If Target.Cells.Count = 1 Then
    Flag = True
    If Left(Target.Address, 3) = "$F$" And Target <> "" Then
    Range("H" & Target.Row).Value = Now
    ThisWorkbook.Save
    End If
End If
  .EnableEvents = True
  .ScreenUpdating = True
End With
Flag = False
End If

myerror:
Flag = False
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

 

[NdNoviceHlp]

Had a bit more time to consider this. Trial 2. Dave

 Private Sub Worksheet_Change(ByVal Target As Range)
Dim Flag As Boolean
On Error GoTo myerror
If Target.Cells.Count = 1 Then
If Left(Target.Address, 3) = "$F$" And Target <> "" Then
    If Not Flag Then
    With ActiveSheet
    .Range("H" & Target.Row).Value = Now
    Flag = True
    End With
    ThisWorkbook.Save
    End If
End If
Flag = True
End If
Exit Sub

myerror:
On Error GoTo 0
MsgBox "Errrorr"
End Sub

 

[JumboCactuar]

Had a bit more time to consider this. Trial 2. Dave

 Private Sub Worksheet_Change(ByVal Target As Range)
Dim Flag As Boolean
On Error GoTo myerror
If Target.Cells.Count = 1 Then
If Left(Target.Address, 3) = "$F$" And Target <> "" Then
    If Not Flag Then
    With ActiveSheet
    .Range("H" & Target.Row).Value = Now
    Flag = True
    End With
    ThisWorkbook.Save
    End If
End If
Flag = True
End If
Exit Sub

myerror:
On Error GoTo 0
MsgBox "Errrorr"
End Sub

Thankyou, I will try both

The 2nd one doesn't set the flag back to 0, is that normal here?

 

[NdNoviceHlp]

I didn't mention that the code wasn't specifically tested but followed previously successful resolutions. The flag always starts as False as it is dimmed within the routine. It remains true (if the conditions are met) while the routine is operating. The problem is that your code changes a sheet value which fires the worksheet change event again before the routine is finished ie. it is recursive. That is why the flag is used to tell the code not to fire while it's already running... at least that's how I remember my previous trial and error learning. HTH. Dave