Macro warning box opens twice

B

BP

I'm running Excel 97 on a new p4 machine running XP SP1 Home edition.
Just recently my workbooks that contain my macros have been "double pumping"
the "the workbook you are opening contains macros" warning box. It appears
when you first open the file from the desktop without Excel being open. The
"MS Excel 97" product ID shows on a blank gray screen like normal, I select
"Enable Macros" and there is a blink, then I get another macro warning box.
I click "Enable Macros" and the workbook opens as usual. It does not occur
if Excel is already open and the file is open from excel.
Any ideas on what is happening and how to correct it?
 
D

Dave Peterson

My (alternate) guess...

You're opening two (well, at least two) workbooks that contain macros.

You could have a couple of files in your "alternate startup file location"
folder.

Try tools|Options|general tab
Clear out that box (not the folder itself) if you have anything typed in there.
 
B

BP

Thanks for the guess, but that line is blank.
The only other thing I can think of is that the file extension association
is causing the behavior. These are my estimating workbooks which I give an
..est file extension. Windows is trained to associate .est with excel.
Another possibility: when a customer asks we to re-price a job I archive a
copy of the old estimate in a separate folder then alter the original
workbook. Maybe having multiple copies causes the behavior?
 
D

Dave Peterson

Multiple copies just sitting on the disk wouldn't cause the problem.

If you look under Window (on the toolbar), how many open workbooks do you see?

In fact, try this:

hit alt-f11 (to see the VBE--where macros live and where that code would be)
hit ctrl-r (to see the project explorer)

This looks a lot like windows explorer. How many projects can you see that are
open.

When I do it, I see:

I see this in mine:
atpvbaen.xls (ATPVBAEN.XLS) (analysis toolpak for VBA stuff)
funcres (FUNCRES.XLA) (anaylsis toolpak for Excel stuff)
personal (personal.xla) (my own set of macros/functions)
VBAProject (book1) (initial empty workbook)

if you see something that you didn't start and looks suspicious, then maybe
that's the one causing trouble.

Anne's guess was that the first workbook you opened was opening another workbook
(in that VBA code). But I don't think xl will prompt you for macros if you open
a workbook via code.

You are only opening one instance of excel, right? Do you see two excel icons
 
B

BP

"If you look under Window (on the toolbar), how many open workbooks do you
see?"
One.

"hit alt-f11. How many projects can you see that are open."
One. VBA Project (WILLIAMS.EST). 8 Worksheets, plus "this workbook" = macro
sheet. Approx. 40 macros on the sheet, mostly defining buttons and named
ranges.

"hit ctrl-r (to see the project explorer)"
Doing this with the workbook active only fills right. Nothing happens with
MS Virtual Basic Editor active.

"the first workbook you opened was opening another workbook"
I don't see any evidence of this happening. And there is no alteration or
changes to the data (code?) on the macro sheet. Is this a good time to
mention that this workbook uses a separate macro sheet that is part of the
workbook. I first created this WB back in 1992 using Excel 2 or 3. It has
worked fine without this behavior under Excel 4.0 and 97, under Windows 3.0,
3.1, 95, 98SE, and XP until just recently, on the 386, the PII, and this P4.
Something changed just recently.

"You are only opening one instance of excel, right?"
Yes, just one. But... on occasion I have opened multiple workbooks from
Windows Explorer rather than from Excel>File>Open. This has opened multiple
Excel windows (Taskbar buttons) rather than the usual 'all open in one Excel
window' that you get with File>Open. Could this action have corrupted
something?

Another clue: Since loading Office 97 on this new XP machine Word had
intermittently displayed a message box stating something to the effect that
"Do you want to revert to the saved version of <File Name>?" whenever I
opened a older document. I found that issue on the KB and the cause was that
the registry info for registered file type was incorrect (KB200915). I
employed the registry edit (solution #5) with no results. Could this be
something similar/related?
 
D

Dave Peterson

Interspersed.
"If you look under Window (on the toolbar), how many open workbooks do you
see?"
One.
Click to expand...

No help there.

"hit alt-f11. How many projects can you see that are open."
One. VBA Project (WILLIAMS.EST). 8 Worksheets, plus "this workbook" = macro
sheet. Approx. 40 macros on the sheet, mostly defining buttons and named
ranges.

"hit ctrl-r (to see the project explorer)"
Doing this with the workbook active only fills right. Nothing happens with
MS Virtual Basic Editor active.
Click to expand...

ctrl-r allows you to see the stuff you described, but doesn't do anything bad if
it's already displayed.

But you only saw one project/workbook in that project explorer window?
"the first workbook you opened was opening another workbook"
I don't see any evidence of this happening. And there is no alteration or
changes to the data (code?) on the macro sheet. Is this a good time to
mention that this workbook uses a separate macro sheet that is part of the
workbook. I first created this WB back in 1992 using Excel 2 or 3. It has
worked fine without this behavior under Excel 4.0 and 97, under Windows 3.0,
3.1, 95, 98SE, and XP until just recently, on the 386, the PII, and this P4.
Something changed just recently.
Click to expand...

I've seen warning messages for the older XLM macros. But these are different
from the standard warning message for the modern VBA macros. Are you sure
you're seeing the same warning message?

I don't recall if you see two messages when you have both types of macros (I
kind of remember that you do--but my memory ain't getting better!)

"You are only opening one instance of excel, right?"
Yes, just one. But... on occasion I have opened multiple workbooks from
Windows Explorer rather than from Excel>File>Open. This has opened multiple
Excel windows (Taskbar buttons) rather than the usual 'all open in one Excel
window' that you get with File>Open. Could this action have corrupted
something?
Click to expand...

I don't think so.

But if you want to change this behavior to open in a single instance, a FAQ at
Debra Dalgleish's site may help:
http://www.contextures.com/xlfaqApp.html#AlreadyOpen


Another clue: Since loading Office 97 on this new XP machine Word had
intermittently displayed a message box stating something to the effect that
"Do you want to revert to the saved version of <File Name>?" whenever I
opened a older document. I found that issue on the KB and the cause was that
the registry info for registered file type was incorrect (KB200915). I
employed the registry edit (solution #5) with no results. Could this be
something similar/related?
Click to expand...

I don't know enough about MSWord to be helpful--but the only time I've seen that
message in excel is when I try to open a workbook that's already open.

========
I'm hoping that the warning messages were different and you just didn't notice
 
B

BP

Hey Dave, I think I found it.
It seems that my File Association for EST was corrupted somehow. Under
Folder Options>File Types the EST listing showed Open With Excel, but the
description beneath was referring to an AutoCAD file extension (!?!). I
deleted the EST listing and recreated it and now everything seems to be
hunky-dory. For the last 20 minutes anyway.
Thanks for your thoughts and your time. I'll post back if I find anything
different, but you I think you can keep this one in your bag of tricks.
 
D

Dave Peterson

Glad you posted back -- so google now knows about it.

(I don't think I've ever seen anyone have your problem (and your solution)
before!)
Hey Dave, I think I found it.
It seems that my File Association for EST was corrupted somehow. Under
Folder Options>File Types the EST listing showed Open With Excel, but the
description beneath was referring to an AutoCAD file extension (!?!). I
deleted the EST listing and recreated it and now everything seems to be
hunky-dory. For the last 20 minutes anyway.
Thanks for your thoughts and your time. I'll post back if I find anything
different, but you I think you can keep this one in your bag of tricks.
Click to expand...
 
Top