Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA-Excel 97

  Asked By: Carolina    Date: Jan 01    Category: MS Office    Views: 812
  

I realize this can be difficult without code, but the code is too
complex to show. Perhaps there is a known issue that sounds familiar.

I have this Multi-sheet Workbook. Lots of VBA - in the sheets, in
Forms and in Code modules. No Class Modules (I just learned what they
are).

THE RUB:
When I do a Drag'n-Drop (Select cell(s), then grab an edge and
move) in Sheet4 of any number of cells (blank or containing data), 17
Procedures in another, Sheet5 fire !!
Sheet4 contains only data I use for combo boxes, text boxes,
lookups and "preferences" used in code in other Sheets, UserForms and
code modules. There is absolutely no code behind this sheet. Zilch.
Not even the Option Explicit statement.

Here are more symptoms I can figure out:
1- The "firing" Subs are *all* of the Combo box_Change() event subs in
Sheet5.

2- They always fire in the same order.

3- A Drag'n-Drop in any other sheet produces no such behavior.

4- There are NO other Procedures calling these _Change() Subs - Yet.
Is VBA reading my mind?

5- They fire roughly in reverse order (module bottom to top), but not
from the very bottom of the module and a bunch of the later ones
appear random.

6- They do not fire in the order that I created them. It seem to be
in the reverse of that order (hard to remember exact order created).

7- I have, in the past, seen this behavior upon a Save, but only the
Drag-N-Drop trigger is consistent and repeatable. I have *NO* Before
Save Event handling Procedures in the entire Workbook code space.
(Only _Change(), Selection_Change() and _Click() Subs).

8- Here's a good one. On the last three Procedures that fire, an
error occurs (understandable due to insufficient variable set-up due
to improper entry) which brings up the VBA Run Time Error
"End-Debug-Help" Dialog Box. If I click END, the next Procedure STILL
FIRES !! (Arrrg ! Phantom BeforeEnd Event ? )

9- Finally, I have for some time while developing this series of
Workbooks, ocassionally had a non-repeatable/semi-repeatable
Application Error which fully crashes Excel - Dr Watson writes into
some event log. Usually something that sounds like a memory access
violation.

It is also important that I have NO code anywhere which is a
BeforeSave event handler nor any BeforeDropOrPaste event handler.

Though I wrote no such code, I even did searches for the Sub names
to make sure nothing actually called them.

Ideas ? VBA 97 bug...?

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Ava Campbell     Answered On: Jan 01

Excel 2003

Sheet1 has one combo box with ListFillRange from a couple of cells  in
Sheet2.

Drag and drop (or ctrl-x/ctrl-v) of any cell in Sheet2 will cause the change
event to fire.

Sounds like slightly eccentric behaviour in Excel.

It'll go away once you stop moving the cells in the constants sheet.
(Patient: "It only hurts when I do this. "Doctor: "So, stop doing that.")

 
Answer #2    Answered By: Brenda Fischer     Answered On: Jan 01

I think you are saying you verified this behavior in Excel 2003, no?

 
Answer #3    Answered By: Tasha Wheeler     Answered On: Jan 01

Yes, I did confirm it with an Excel 2003 workbook.

No, I wouldn't have expected textbox change subs to fire. The text boxes
wouldn't be linked to the second sheet. The combo boxes ARE linked to the
second sheet, and therefore are firing when that second sheet is changed in
some particular ways.

No idea why the supposedly unassociated cell moves trigger this, but they
do, and have over several levels of Excel.

 
Didn't find what you were looking for? Find more on VBA-Excel 97 Or get search suggestion and latest updates.




Tagged: