Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code

[ker_01]

I have two cells with data validation (each using a named range/list that
brings in data from another sheet).

When one changes, it runs through my code and works just fine.

When the other data validation changes, one of the things I have to do is
re-set the other data validation cell to the default value. This part is
working fine, except that it triggers the worksheet_change event to run a
second time.

Is there an elegant way to catch/ignore the worksheet_change event when
changes are caused by code instead of user interaction?

Thanks,
Keith

 

[Thomas [PBD]]

ker_01,

Couldnt you update the second set first?

 

[Per Jessen]

Hi Keith

Turn off events handling before your macro change the cells.

Application.EnableEvents= False

Just remeber to turn it on again =True

Regards,
Per

 

[Rick Rothstein]

You would use the EnableEvents property of the Application object. Here is
one of the many way to structure it...

Private Sub Worksheet_Change(ByVal Target As Range)
If <Your_Test_Condition> = True Then
On Error GoTo CleanUp
Application.EnableEvents = False
'
' <<Your code goes here>>
'
End If
CleanUp
Application.EnableEvents = True
End Sub

Note: The On Error trap is needed in case your code errors out... if you
don't turn the EnableEvents back on, it remains off for other macros that
may be executed afterwards.

 

[Dave Peterson]

application.enableevents = false
'your code that would have triggered any event
application.enableevents = true

 

[ker_01]

The first list is a list of departments, the second is dates. If a
department is already selected and the date is changed, the code opens
another worksheet, autofilters based on the department and date, and copies
the results back over to this master sheet. Changing the date is only one
worksheet_change event, as desired.

When the department is changed, I have to default back to the earliest date-
so I reset (or am trying to reset, that will be my next new thread) the
date. It appears that when the worksheet_change event includes code that
changes the other cell, I'm triggering the worksheet_change event a second
time, and it runs the sub that second time as soon as the first run is
complete.

The approach I'm using is below- I welcome any advice.
Thanks,
Keith

Private Sub Worksheet_Change(ByVal Target As Range)

worksheetupdatecount = worksheetupdatecount + 1
Debug.Print worksheetupdatecount 'to verify the sub is running twice

If Target.Address = "$B$2" Then 'dept changed
'graph updates are handled with dynamic named ranges
'just reset the date back to earliest date possible for the new dept
S = Sheet12.Range("E2").Validation.Formula1
Sheet12.Range("E2").Value = Range(S)(1) 'This isn't working yet
Set Target = Sheet12.Range("E2") 'so the next if statement will run as
well
End If

If Target.Address = "$E$2" Then 'date changed or reset

'Collect autofilter information
SortDept = Sheet12.Range("B2").Value
SortDate = Target.Value

'clear old data
Sheet12.Activate
Sheet12.Rows("28:5000").Select
Selection.Delete Shift:=xlUp
Sheet12.Range("A1").Activate

'get the new data and copy it over to Sheet12
'then turn off the autofilter to avoid messing up other blocks of code
Sheet16.Select
Sheet16.Cells.Select
Sheet16.Range("A1").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=10, Criteria1:=SortDept
Selection.AutoFilter Field:=20, Criteria1:=SortDate
Sheet16.Range("A1:X50001").Select
Selection.Copy
Sheet12.Select
Sheet12.Range("A28").Select
ActiveSheet.Paste
Sheet16.Activate
Application.CutCopyMode = False
Selection.AutoFilter
Sheet16.Range("A1").Select
Sheet12.Activate

End If
End Sub

ker_01,

Couldnt you update the second set first?

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.

I have two cells with data validation (each using a named range/list that
brings in data from another sheet).

When one changes, it runs through my code and works just fine.

When the other data validation changes, one of the things I have to do is
re-set the other data validation cell to the default value. This part is
working fine, except that it triggers the worksheet_change event to run a
second time.

Is there an elegant way to catch/ignore the worksheet_change event when
changes are caused by code instead of user interaction?

Thanks,
Keith

 

[ker_01]

Awesome, thanks to all that responded- This looks like it will solve my
problem.
Best,
Keith