Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EnableEvents and DisplayAlerts problems
Hi,
I'm having trouble getting a couple of things to work properly - Application.EnableEvents and Application.DisplayAlerts. I assume the two problems are related but I don't know the cause. For the EnableEvents: I have two comboboxes on a spreadsheet. When the first one is changed by the user, it updates the contents of the second combobox. This procedure ends by calling a procedure (FilterSub) that is also run when the second box is updated by the user. This procedure filters some data and cuts and pastes it to a worksheet. To prevent automatic updates of the second combobox causing the second procedure to run twice, I have set Application.EnableEvents = False. However, it still runs twice and I can't figure out why! It starts running the change event at the line indicated below: Sub FillCombo(Category As Collection, MyCombo As MSForms.ComboBox) Application.EnableEvents = False Dim Item With MyCombo .Clear 'The following procedure runs after this -I don't want it to! .AddItem "<All" For Each Item In Category If Item < "" Then .AddItem Item Next Item .ListIndex = 0 End With End Sub Application.EnableEvents is still set to false when the following code runs: Private Sub ComboBox2_Change() FilterSub End Sub Am I using the EnableEvents property for the wrong thing? For DisplayAlerts: Before closing I have this: Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.DisplayAlerts = False End Sub but it still asks if I want to save. I've also tried adding/substituting ActiveWorkbook.Saved = True but it still doesn't work. Can anybody help? I'm using Office 2000. TIA, Derek |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
EnableEvents and DisplayAlerts problems
EnableEvents has no affect on objects placed on a worksheet. It only
affects pure Excel workbook/worksheet/etc., events. Just like it has no affect on userforms. You have to set your own flags, e.g.: Dim NoEvents as Boolean Sub FillCombo(Category As Collection, MyCombo As MSForms.ComboBox) NoEvents = True ...other code NoEvents = False End Sub Private Sub ComboBox2_Change() If NoEvents = False Then FilterSub End if End Sub -- Jim Rech Excel MVP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
EnableEvents and DisplayAlerts problems
Application.EnableEvents only pertains to the Events in the Excel object
library. Controls are in the MSforms object library and are not governed by this setting. You would have to use a boolean variable that is checked by the code as the first line to determine whether to run or not. Have the triggering code set the boolean to true Public bBlockEvents as Boolean Sub SomeEventProcedure() bBlockEvents = True ' code that triggers myprocedure bBlockEvents = False End Sub Sub MyProcedure() if bBlockEvents then exit sub ' current code End Sub ThisWorkbook.Saved = True should work, but I believe in the original xl2000, there was a bug associated with this. I don't recall the particulars. -- Regards, Tom Ogilvy "Derek Gadd" wrote in message om... Hi, I'm having trouble getting a couple of things to work properly - Application.EnableEvents and Application.DisplayAlerts. I assume the two problems are related but I don't know the cause. For the EnableEvents: I have two comboboxes on a spreadsheet. When the first one is changed by the user, it updates the contents of the second combobox. This procedure ends by calling a procedure (FilterSub) that is also run when the second box is updated by the user. This procedure filters some data and cuts and pastes it to a worksheet. To prevent automatic updates of the second combobox causing the second procedure to run twice, I have set Application.EnableEvents = False. However, it still runs twice and I can't figure out why! It starts running the change event at the line indicated below: Sub FillCombo(Category As Collection, MyCombo As MSForms.ComboBox) Application.EnableEvents = False Dim Item With MyCombo .Clear 'The following procedure runs after this -I don't want it to! .AddItem "<All" For Each Item In Category If Item < "" Then .AddItem Item Next Item .ListIndex = 0 End With End Sub Application.EnableEvents is still set to false when the following code runs: Private Sub ComboBox2_Change() FilterSub End Sub Am I using the EnableEvents property for the wrong thing? For DisplayAlerts: Before closing I have this: Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.DisplayAlerts = False End Sub but it still asks if I want to save. I've also tried adding/substituting ActiveWorkbook.Saved = True but it still doesn't work. Can anybody help? I'm using Office 2000. TIA, Derek |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
EnableEvents and DisplayAlerts problems
The workaround might have been to use the old AutoClose event. Set Saved =
True in that and don't use beforeClose. -- Regards, Tom Ogilvy "Derek Gadd" wrote in message om... Hi, I'm having trouble getting a couple of things to work properly - Application.EnableEvents and Application.DisplayAlerts. I assume the two problems are related but I don't know the cause. For the EnableEvents: I have two comboboxes on a spreadsheet. When the first one is changed by the user, it updates the contents of the second combobox. This procedure ends by calling a procedure (FilterSub) that is also run when the second box is updated by the user. This procedure filters some data and cuts and pastes it to a worksheet. To prevent automatic updates of the second combobox causing the second procedure to run twice, I have set Application.EnableEvents = False. However, it still runs twice and I can't figure out why! It starts running the change event at the line indicated below: Sub FillCombo(Category As Collection, MyCombo As MSForms.ComboBox) Application.EnableEvents = False Dim Item With MyCombo .Clear 'The following procedure runs after this -I don't want it to! .AddItem "<All" For Each Item In Category If Item < "" Then .AddItem Item Next Item .ListIndex = 0 End With End Sub Application.EnableEvents is still set to false when the following code runs: Private Sub ComboBox2_Change() FilterSub End Sub Am I using the EnableEvents property for the wrong thing? For DisplayAlerts: Before closing I have this: Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.DisplayAlerts = False End Sub but it still asks if I want to save. I've also tried adding/substituting ActiveWorkbook.Saved = True but it still doesn't work. Can anybody help? I'm using Office 2000. TIA, Derek |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
aauugghhh...#div/o problems & various average formula problems | Excel Worksheet Functions | |||
Application.EnableEvents | Excel Worksheet Functions | |||
Way around CUT problems... | Excel Discussion (Misc queries) | |||
application.EnableEvents | Excel Discussion (Misc queries) | |||
DisplayAlerts and ScreenUpdate will not set to False | New Users to Excel |