Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code does
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run VBA code only worksheet change, but don't trigger worksheet_ch
ker_01,
Couldnt you update the second set first? -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "ker_01" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run VBA code only worksheet change, but don't triggerworksheet_change event based on what the code does
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 On 3 Okt., 22:19, "ker_01" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code does
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. -- Rick (MVP - Excel) "ker_01" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run VBA code only worksheet change, but don't triggerworksheet_change event based on what the code does
application.enableevents = false
'your code that would have triggered any event application.enableevents = true ker_01 wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run VBA code only worksheet change, but don't trigger worksheet_change event
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 "Thomas [PBD]" wrote in message ... ker_01, Couldnt you update the second set first? -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "ker_01" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code does
Awesome, thanks to all that responded- This looks like it will solve my
problem. Best, Keith "Rick Rothstein" wrote in message ... 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. -- Rick (MVP - Excel) "ker_01" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trigger Event Code | Excel Programming | |||
Code to trigger drop down-select event | Excel Programming | |||
Code WAY too slow... (worksheet_change event) | Excel Programming |