Opening a file stops a Macro

[Bob Myers]

My spreadsheet (main.xls) has a number of macros. One of them (macro1)
tries to open another spreadsheet (file.xls). However, when file.xls opens,
the macro stops, rather than continuing with the rest of its code. I have
similar code in macro2, but it seems to work as expected.

An excerpt from macro1 code:
..
ChDir (ActiveWorkbook.Path) ' main.xls and file.xls are
in the same folder on C:
..
Workbooks.Open (file.xls) ' file.xls does not
include the path
x = 1
..
Running macro1, file.xls opens and macro1 stops. If the breakpoint is set
on the x = 1 line, execution never gets there.

If the Breakpoint is set on the Open line, execution halts there, but
pressing F8 (or F5) will cause macro1 to continue normally. Normally
includes executing similar code farther on in macro1.

Any suggestions? I've tried everything I can think of. FYI, I'm fairly new
to Excel, and particularly to VBA. I am experienced in several forms of
Basic, and a couple other high level languages.

Perhaps there's a workaround. All I really want to do is open file.xls,
copy a little bit of stuff into it, and then close it. I plan on doing this
to half a dozen files.xls.

Respectfully submitted,
Bob Myers
(e-mail address removed)

 

[Nick Hodge]

Bob

VBA runs sequentially and therefore I would suspect that the workbook you
are opening is trying to get input from the user or similar. Until that
happens the code will not continue. Do you have alerts switched off in the
code or screenupdating off? This may hide something the workbook opening
wants to do

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk

 

[Dave Peterson]

My guess is that you're using a shortcut key to start your macro. And that
shortcut key includes a shift key.

Remove the shift key from that shortcut combination and try it again.

Holding the shiftkey when you open a workbook stops the open macros from
running. It also confuses excel/vba into thinking it should stop.