Listbox Change events triggering when i "Save As"

[magnuspaterson]

I've got a setup with 2 excel spreadsheets: a Parent and a Child. The
child contains a number of Listboxes that when changed run different
macros to update the data.

The Parent has a VBA macro that opens the Child, ammends some data and
then Saves the child as a different filename using the Save As
function. The SaveAs function then runs the Listbox macros in the
Child, but these can't complete properly for some unknown reason.
Accordingly I get VBA crashing out.

I've spent the majority of the afternoon searching through various
groups on the web to try and find a way of disabling the Listbox
macros (or forcing them to run before the Save As) or any other
workaround i can find. And I think i've tried every possible version
of Enabling/Disabling events to no avail.

Can anyone help me. Thanks.

 

[NoodNutt]

G'day Magnus

If you want to disable any VBA statements in your code simply type "REM"
(minus the quotes) at the front of the command line, it will then turn it
red and it will not execute that portion of the code.

Paste your code here so we can look it over and pinpoint any anomalies.

HTH
Mark.

 

[londonexplorers]

G'day Magnus

If you want to disable any VBA statements in your code simply type "REM"
(minus the quotes) at the front of the command line, it will then turn it
red and it will not execute that portion of the code.

Paste your code here so we can look it over and pinpoint any anomalies.

HTH
Mark.

Thanks Mark,

Thats a trick I wasn't aware of but I don't think it will solve the
problem. Because the listbox macros are needed for daily usage of the
spreadsheet, I can't stop the macros from running at all times using
the above idea. Instead I need to be able to either stop the automatic
running durring the Save As or find another way around the problem.
Because the Listbox macros are starting on automatically i also can't
pass a variable through to them that could be used to "REM" the code.

If I was initiating the macros this would work well.

I can post up code if you like, but i just think that will confuse the
issue more. I'm just trying to prevent the Listbox events from running
durring the Save As, but to leave them running the rest of the time.

 

[londonexplorers]

I think i've found a way around this. I've stopped using Listboxes.
Instead i'm using ComboBoxes that i've changed into effective dropdown
lists. They seem to be a lot more stable than the Listboxes.

It solves the problems for just now anyway.

 

[Dave Peterson]

If you have an old version of your workbook, maybe you could try this (I think
I'm remembering it correctly)...

If you have a linked cell for that listbox, make sure it's on a different
worksheet.
If you have the listfillrange on the same worksheet, put it on a different
worksheet.

Another option would be to drop both the linkedcell and listfillrange and use
code to populate the listbox and assign the value to a cell.