[RESOLVED] MS Excel Events

[dy1jaw]

Hi all,

Is there any way that i can do so as not to trigger the Worksheet_Change event of the Excel Worksheet? I would like to write to 2 cells of the worksheet but my program is looping because it keeps on getting into the Change event.

Here's my code.

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 3 And (ActiveCell.Row Mod 2) <> 0 Then
ActiveCell.Offset(-1, 1).Value = ActiveCell.Offset(-1, 0).Value + Sheets_
(1).Cells(ActiveCell.Row - 1, ActiveCell.Column)
ActiveCell.Offset(-2, 1).Value = ActiveCell.Offset(-2, 0).Value + Sheets_
(1).Cells(ActiveCell.Row - 2, ActiveCell.Column)
End If
End Sub

thanks a lot!

[Bruce Fox]

Maybe by using a Flag like:

Private Sub Worksheet_Change(ByVal Target As Range)
Static blnDone As Boolean
 
    If Not blnDone And (ActiveCell.Column = 3 And (ActiveCell.Row Mod 2) <> 0) Then
        ActiveCell.Offset(-1, 1).Value = ActiveCell.Offset(-1, 0).Value + Sheets _
        (1).Cells(ActiveCell.Row - 1, ActiveCell.Column)
        ActiveCell.Offset(-2, 1).Value = ActiveCell.Offset(-2, 0).Value + Sheets _
        (1).Cells(ActiveCell.Row - 2, ActiveCell.Column)
        blnDone = True
    End If
    
End Sub

I haven't tested it, but you may get the idea

[si_the_geek]

A little issue Bruce (which I'm sure you'll be kicking yourself for!), the flag needs to be changed one way before the work starts, and changed back once finished, eg:

Private Sub Worksheet_Change(ByVal Target As Range)
Static blnWorking As Boolean
   If Not(blnWorking) Then
     blnWorking = True
 
    If (ActiveCell.Column = 3 And (ActiveCell.Row Mod 2) <> 0) Then
        ActiveCell.Offset(-1, 1).Value = ActiveCell.Offset(-1, 0).Value + Sheets _
        (1).Cells(ActiveCell.Row - 1, ActiveCell.Column)
        ActiveCell.Offset(-2, 1).Value = ActiveCell.Offset(-2, 0).Value + Sheets _
        (1).Cells(ActiveCell.Row - 2, ActiveCell.Column)
    End If
    blnWorking = False
  End If
End Sub

[medison]

FWIW, I always use the EnableEvents property of the Application object:

Private Sub Worksheet_Change(ByVal Target As Range)
     Application.EnableEvents = False
     'Your Code Here
     Application.EnableEvents = True
End Sub

HTH

[Bruce Fox]

Hi si_the_geek,

I left it as is because I figured that once that particular piece of code excutes, that it is no longer requried for that session. Opps maybe I shouln't have assumed that...

Have a great weekend

Bruce.

[dy1jaw]

thanks a lot

[Bruce Fox]

FWIW, I always use the EnableEvents property of the Application object:

Private Sub Worksheet_Change(ByVal Target As Range)
     Application.EnableEvents = False
     'Your Code Here
     Application.EnableEvents = True
End Sub

HTH