Excel 2003 SP2 fires events without reason when saving workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
|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
 
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:
 
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
| 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
|
 
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:
 
I have also managed to get excel vba to write to random cells on a
Well, aren't you having an interesting day. Could this be your dream?<g>

--
Jim
| 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
| > | > | 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
| > |
| >
| >
| >
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combobox_change event fires when a workbooks is saved 3
Combobox_Change event fires on workbook close 3
combobox change fires on workbook close when textcol <>1 or -1 2
thisworkbook.saveAs triggers change event? 3
Addin not allowing thisworkbook.save in before save event 3
Worksheet Change event not firing but Worksheet activate event does 1
ComboBox Events Fire Many Times 4
Problem with ComboBox, dynamic range and change event 2

Back
Top