Problem stopping a macro sheet from opening -- help!

[Steve Maser]

Here's what I have...

Two *very old* excel spreadsheets that I'm using with Excel 2004 (or
"Excel X" -- either one.)


One file called "spreadsheet"

One file called "macros".


The "macros" spreadsheet is chock full of "version 4.0" macros.


When the "spreadsheet" file is opened, the "macros" spreadsheet is
launched and a "begin" macro is run.


I need to figure out how to stop the launching of the "macros" file
when the "spreadsheet" file is launched.


All the on-line help seems to imply that there's some "auto_open" macro
being run, but when I look at all the Macros (which I can't delete
because they are "version 4.0" macros), there is *no* auto_open macro
listed.


So, what am I missing?

What is in "spreadsheet" that's causing the call to open "macros" at
startup? And how do I stop it?


Thanks!

- Steve

 

[JE McGimpsey]

What is in "spreadsheet" that's causing the call to open "macros" at
startup? And how do I stop it?

Do you have a macro sheet in "spreadsheet" (it may be hidden). You
should be able to delete the macros from that...

To disable all macros, open the file with the Shift key held down.

 

[Steve Maser]

Do you have a macro sheet in "spreadsheet" (it may be hidden). You
should be able to delete the macros from that...

To disable all macros, open the file with the Shift key held down.

If I open the "spreadsheet" with the shift key down and look in
Tools->Macro->Macros, there are no macros listed.

If "auto_open" is *hidden*, how would I find it?

- Steve

 

[JE McGimpsey]

If I open the "spreadsheet" with the shift key down and look in
Tools->Macro->Macros, there are no macros listed.

If "auto_open" is *hidden*, how would I find it?

Instead of Tools/Macro/Macros, look for a Macro SHEET - it's where
macros were stored in XL4M (The default name would be Macro1). To unhide
sheets, choose Format/Sheet/Unhide...

Another potential source for the XL4M message is defined names. Check
Insert/Names/Define to see if any of your defined names use XL4M.

 

[Steve Maser]

Instead of Tools/Macro/Macros, look for a Macro SHEET - it's where
macros were stored in XL4M (The default name would be Macro1). To unhide
sheets, choose Format/Sheet/Unhide...

There are no additional Sheets as far as I can see (the "unhide" is
greyed out as an option to select.)


Same for the "macros" spreadsheet

Another potential source for the XL4M message is defined names. Check
Insert/Names/Define to see if any of your defined names use XL4M.

Insert/Names/Define is *also* greyed out.


If I open the "macros" spreadsheet and go to Insert/Names/Define and
purge *all* the names...

Then when I open the "spreadsheet" file, I'm still greeted with "do I
want to enable macros" and when I say OK, I then get "Cannot find
Macros!Begin, which has been assigned to run each time <spreadsheet> is
opened..."


So the "spreadsheet" file is *still* trying to auto_open (and run) the
"Macros" spreadsheet.


The problem has to be in the "data" spreadsheet.

Where else might this "auto_open" be?


If it's a *link* (and there *is* a link listed) that's doing this, I
don't seem to be able to "break" the link any way.


Any more ideas? I'll keep trying...

- Steve

 

[JE McGimpsey]

Where else might this "auto_open" be?

Is there a Workbook_Open() macro in the ThisWorkbook code module?

 

[Steve Maser]

Is there a Workbook_Open() macro in the ThisWorkbook code module?

I do not believe so.


If I open the "spreadsheet" and go to:

Tools --> Macro --> Visual Basic Editor

select VBAProject (Spreadsheet).

expand the "Microsoft Excel Objects"

Select "ThisWorkbook"

Then go to View --> Code

Change "(General)" to "Workbook", then "(Declarations)" changes to
"Open"


There *is* a listing for:

Private Sub Workbook_Open()

End Sub


But that's it.


If this is it, I can't figure out what to do next. There's no way to
delete it as far as I can tell.


If that's *not* it, what would I be looking for here?


(I'd be happy to send you a copy of this spreadsheet if you'd like --
just let me know...)

 

[JE McGimpsey]

If this is it, I can't figure out what to do next. There's no way to
delete it as far as I can tell.


If that's *not* it, what would I be looking for here?

You actually created the macro when you chose Open from the right-hand
dropdown. You can delete it just like any other macro - select it and
hit the Delete key.

I'm about out of ideas. If you want to send me a workbook (zipped or
stuffed, from your despammed address), I'd be happy to take a look.

 

[Steve Maser]

I'm about out of ideas. If you want to send me a workbook (zipped or

stuffed, from your despammed address), I'd be happy to take a look.

done.

If you have any thoughts about it after you've looked at it -- feel
free to share them here!

Thanks!

- Steve

 

[JE McGimpsey]

If you have any thoughts about it after you've looked at it -- feel
free to share them here!

In the Encumbrances Spreadsheet, you had a Defined Name: Autpen,
defined as

='<path>EncumbranceMacros'!Begin

When I deleted that, the sheet opened without calling the macro sheet.

 

[Hill Person]

It's funny. I'm trying to do the reverse of you and put in an auto open macro.
I swear that you used to create a 'name' called auto_open and give it as a
value the name of the macro to run.

I still have workbooks that I think i did this for. What i find interesting is that
they no longer show a 'name' auto_open. Help also no longer seems to tell
you how to set up an auto_open macro.

I wonder if they have withdrawn the ability to display this 'name'. You could
try defining a 'name' auto_open with a blank content. That might override
any hidden name.

Just as lost as you but from the other side of the glass.

-----Original Message-----

 

[JE McGimpsey]

It's funny. I'm trying to do the reverse of you and put in an auto
open macro. I swear that you used to create a 'name' called auto open
and give it as a value the name of the macro to run.

I think you could do that with XL4M macros (and presumably still could),
but I've never seen it done with VBA.

I still have workbooks that I think i did this for. What i find
interesting is that they no longer show a 'name' auto open. Help also
no longer seems to tell you how to set up an auto open macro.

Auto Open macros in XL have been deprecated in favor of the
Workbook_Open event macro. Workbook-level event macros are put in the
ThisWorkbook code module. An example is

Private Sub Workbook_Open()
MsgBox "Workbook is open!"
End Sub

Auto Open is still supported. Put the macro in a regular code module:

Public Sub Autpen()
MsgBox "Workbook is open!"
End Sub

For more on code modules and event macros see

http://www.mcgimpsey.com/excel/modules.html

and

http://cpearson.com/excel/events.htm