Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 SP2 fires events without reason when saving workbook
I have a very strange problem with events, to which I have not found an answer.
I have a workbook with dynamic comboboxes (selection in one changes listfillrange in another etc.). When I save the workbook (whether from File, SaveAs or by VBA code), several change events fire in these comboboxes. This causes unwanted behaviour. And yes, I have the Application.enableEvents set as false. Would anyone have a clue why events fire without reason, or how to circumvent the problem? Alerion |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 SP2 fires events without reason when saving workbook
The events of userforms or Control Toolbar controls on worksheets are not
controlled by Application.EnableEvents. For these items you have to establish your own enable events mechanism. For instance, you could have a public variable like: Public EventsEnabled As Boolean in the standard module of your choice. And then for each control you could refer to it: Private Sub ListBox1_Click() If EventsEnabled Then ''Run my code End If End Sub You would set EventsEnabled to True in normal user use and False before a save or any other time you do not want control event code to run. -- Jim "Alerion" wrote in message ... |I have a very strange problem with events, to which I have not found an answer. | I have a workbook with dynamic comboboxes (selection in one changes | listfillrange in another etc.). | When I save the workbook (whether from File, SaveAs or by VBA code), several | change events fire in these comboboxes. This causes unwanted behaviour. | And yes, I have the Application.enableEvents set as false. | | Would anyone have a clue why events fire without reason, or how to | circumvent the problem? | | Alerion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 SP2 fires events without reason when saving workboo
Thank you for you idea. This is one workaround that I have used. And it
worked pretty well: The code in combobox_change was not executed. However, one of my problems didn't end there, and that is a problem that I am really confused about. I will try to explain what my code does in words. Right now I'm skinning the code to simple basics and trying to pinpoint the cause. Last one of these comboboxes has a list of product names. When selected, it writes the combobox value to a cell. Then (with some criteria) it fetches the product code from a list in another worksheet and writes that to another cell. I also have a checkbox that changes the language of the product name, Finnish to English and vice versa. It looks for the product code in this aforementioned list and when found, it swithches the product name in the aforementioned cell to the other language. So far so good. And now we get to interesting events. After this is done, I click a button that saves the workbook. Simplified code: Private sub btn_save.click thisworkbook.saveas (myfilename) end sub Private sub cbo1Prod.change if EnableEvents = true then code code code End if End sub I have combed through the code with F8. After the saveas statement, the change event is fired. It does not execute the code in the change event at all. But when the "Private sub cbo1Prod.change" line is highlighted with yellow, the product name in the cell has turned back to the original language. This is very strange. The checkbox code was not run, neither the code in the change event. Any ideas? :) Alerion It does not execute the code "Jim Rech" kirjoitti: The events of userforms or Control Toolbar controls on worksheets are not controlled by Application.EnableEvents. For these items you have to establish your own enable events mechanism. For instance, you could have a public variable like: Public EventsEnabled As Boolean in the standard module of your choice. And then for each control you could refer to it: Private Sub ListBox1_Click() If EventsEnabled Then ''Run my code End If End Sub You would set EventsEnabled to True in normal user use and False before a save or any other time you do not want control event code to run. -- Jim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 SP2 fires events without reason when saving workboo
Is your real SaveClick code like this?:
Private sub SaveBtn_Click Application.EnableEvents = False EventsEnabled = False thisworkbook.saveas myfilename Application.EnableEvents = True EventsEnabled = True End sub If so I cannot imagine what is setting back the cell values. I don't suppose you have a slimmed down version that does this that you can send me? -- Jim "Alerion" wrote in message ... | Thank you for you idea. This is one workaround that I have used. And it | worked pretty well: The code in combobox_change was not executed. | | However, one of my problems didn't end there, and that is a problem that I | am really confused about. I will try to explain what my code does in words. | Right now I'm skinning the code to simple basics and trying to pinpoint the | cause. | | Last one of these comboboxes has a list of product names. When selected, it | writes the combobox value to a cell. Then (with some criteria) it fetches the | product code from a list in another worksheet and writes that to another cell. | | I also have a checkbox that changes the language of the product name, | Finnish to English and vice versa. It looks for the product code in this | aforementioned list and when found, it swithches the product name in the | aforementioned cell to the other language. So far so good. And now we get to | interesting events. | | After this is done, I click a button that saves the workbook. | | Simplified code: | Private sub btn_save.click | thisworkbook.saveas (myfilename) | end sub | | Private sub cbo1Prod.change | if EnableEvents = true then | code code code | End if | End sub | | I have combed through the code with F8. After the saveas statement, the | change event is fired. It does not execute the code in the change event at | all. But when the "Private sub cbo1Prod.change" line is highlighted with | yellow, the product name in the cell has turned back to the original language. | | This is very strange. The checkbox code was not run, neither the code in the | change event. | | Any ideas? :) | | Alerion | | | It does not execute the code | "Jim Rech" kirjoitti: | | The events of userforms or Control Toolbar controls on worksheets are not | controlled by Application.EnableEvents. For these items you have to | establish your own enable events mechanism. For instance, you could have a | public variable like: | | Public EventsEnabled As Boolean | | in the standard module of your choice. And then for each control you could | refer to it: | | Private Sub ListBox1_Click() | If EventsEnabled Then | ''Run my code | End If | End Sub | | You would set EventsEnabled to True in normal user use and False before a | save or any other time you do not want control event code to run. | | -- | Jim | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 SP2 fires events without reason when saving workboo
Yes, it has all those elements you stated, and in that partiucular order
aswell. I don't yet have skinned the code. I will do it tomorrow and send it. I have also managed to get excel vba to write to random cells on a protected worksheet without asking it to do so, but that's beside the point in this matter. :D Till tomorrow, Alerion "Jim Rech" kirjoitti: Is your real SaveClick code like this?: Private sub SaveBtn_Click Application.EnableEvents = False EventsEnabled = False thisworkbook.saveas myfilename Application.EnableEvents = True EventsEnabled = True End sub If so I cannot imagine what is setting back the cell values. I don't suppose you have a slimmed down version that does this that you can send me? -- Jim "Alerion" wrote in message ... | Thank you for you idea. This is one workaround that I have used. And it | worked pretty well: The code in combobox_change was not executed. | | However, one of my problems didn't end there, and that is a problem that I | am really confused about. I will try to explain what my code does in words. | Right now I'm skinning the code to simple basics and trying to pinpoint the | cause. | | Last one of these comboboxes has a list of product names. When selected, it | writes the combobox value to a cell. Then (with some criteria) it fetches the | product code from a list in another worksheet and writes that to another cell. | | I also have a checkbox that changes the language of the product name, | Finnish to English and vice versa. It looks for the product code in this | aforementioned list and when found, it swithches the product name in the | aforementioned cell to the other language. So far so good. And now we get to | interesting events. | | After this is done, I click a button that saves the workbook. | | Simplified code: | Private sub btn_save.click | thisworkbook.saveas (myfilename) | end sub | | Private sub cbo1Prod.change | if EnableEvents = true then | code code code | End if | End sub | | I have combed through the code with F8. After the saveas statement, the | change event is fired. It does not execute the code in the change event at | all. But when the "Private sub cbo1Prod.change" line is highlighted with | yellow, the product name in the cell has turned back to the original language. | | This is very strange. The checkbox code was not run, neither the code in the | change event. | | Any ideas? :) | | Alerion | | | It does not execute the code | "Jim Rech" kirjoitti: | | The events of userforms or Control Toolbar controls on worksheets are not | controlled by Application.EnableEvents. For these items you have to | establish your own enable events mechanism. For instance, you could have a | public variable like: | | Public EventsEnabled As Boolean | | in the standard module of your choice. And then for each control you could | refer to it: | | Private Sub ListBox1_Click() | If EventsEnabled Then | ''Run my code | End If | End Sub | | You would set EventsEnabled to True in normal user use and False before a | save or any other time you do not want control event code to run. | | -- | Jim | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 SP2 fires events without reason when saving workboo
I have also managed to get excel vba to write to random cells on a
protected worksheet without asking it to do so Well, aren't you having an interesting day. Could this be your dream?<g -- Jim "Alerion" wrote in message ... | Yes, it has all those elements you stated, and in that partiucular order | aswell. I don't yet have skinned the code. I will do it tomorrow and send it. | | I have also managed to get excel vba to write to random cells on a protected | worksheet without asking it to do so, but that's beside the point in this | matter. :D | | Till tomorrow, | | Alerion | | | "Jim Rech" kirjoitti: | | Is your real SaveClick code like this?: | | Private sub SaveBtn_Click | Application.EnableEvents = False | EventsEnabled = False | thisworkbook.saveas myfilename | | Application.EnableEvents = True | EventsEnabled = True | End sub | | If so I cannot imagine what is setting back the cell values. I don't | suppose you have a slimmed down version that does this that you can send me? | | -- | Jim | "Alerion" wrote in message | ... | | Thank you for you idea. This is one workaround that I have used. And it | | worked pretty well: The code in combobox_change was not executed. | | | | However, one of my problems didn't end there, and that is a problem that I | | am really confused about. I will try to explain what my code does in | words. | | Right now I'm skinning the code to simple basics and trying to pinpoint | the | | cause. | | | | Last one of these comboboxes has a list of product names. When selected, | it | | writes the combobox value to a cell. Then (with some criteria) it fetches | the | | product code from a list in another worksheet and writes that to another | cell. | | | | I also have a checkbox that changes the language of the product name, | | Finnish to English and vice versa. It looks for the product code in this | | aforementioned list and when found, it swithches the product name in the | | aforementioned cell to the other language. So far so good. And now we get | to | | interesting events. | | | | After this is done, I click a button that saves the workbook. | | | | Simplified code: | | Private sub btn_save.click | | thisworkbook.saveas (myfilename) | | end sub | | | | Private sub cbo1Prod.change | | if EnableEvents = true then | | code code code | | End if | | End sub | | | | I have combed through the code with F8. After the saveas statement, the | | change event is fired. It does not execute the code in the change event at | | all. But when the "Private sub cbo1Prod.change" line is highlighted with | | yellow, the product name in the cell has turned back to the original | language. | | | | This is very strange. The checkbox code was not run, neither the code in | the | | change event. | | | | Any ideas? :) | | | | Alerion | | | | | | It does not execute the code | | "Jim Rech" kirjoitti: | | | | The events of userforms or Control Toolbar controls on worksheets are | not | | controlled by Application.EnableEvents. For these items you have to | | establish your own enable events mechanism. For instance, you could | have a | | public variable like: | | | | Public EventsEnabled As Boolean | | | | in the standard module of your choice. And then for each control you | could | | refer to it: | | | | Private Sub ListBox1_Click() | | If EventsEnabled Then | | ''Run my code | | End If | | End Sub | | | | You would set EventsEnabled to True in normal user use and False before | a | | save or any other time you do not want control event code to run. | | | | -- | | Jim | | | | | |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 SP2 fires events without reason when saving workbook
Thanks I'm getting so close to having this solved, with your help, I just
have one really dumb question and that is how come even when I've declared the variable as Public bypassCombo as boolean --- varibale used to skip the code in the ComboChange Event Handler I set the value to true in the worksheet before close event.... that all seems to go well, however the event still fires which I expected it to do...my difficulty now is that for some strange reason my bypassCombo has reverted back to FALSE so here is the dumb question..... Where should the Public bypassCombo as Boolean be decalred Worksheet, WorkBook, or Module Level? I think I need a tad more study on the Excel Object Model and how the events bubble up and such? Any quick thoughts? Do you know of a Print that shows the object model i detail so I can learn this mess once and foreall? Thanks a bunch, Rick "Alerion" wrote: I have a very strange problem with events, to which I have not found an answer. I have a workbook with dynamic comboboxes (selection in one changes listfillrange in another etc.). When I save the workbook (whether from File, SaveAs or by VBA code), several change events fire in these comboboxes. This causes unwanted behaviour. And yes, I have the Application.enableEvents set as false. Would anyone have a clue why events fire without reason, or how to circumvent the problem? Alerion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving a workbook in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 - Shared Workbook NOT Saving Changes | Excel Worksheet Functions | |||
Saving a workbook within VBA without executing events | Excel Programming | |||
Why does Excel 2003 freeze when saving a workbook that has importe | Excel Worksheet Functions | |||
Excel 2003: Alignment problems saving workbook as prn file. | Excel Programming |