Problem (bug?) in the way Excel 2007 doesn't automatically load ma

[GrayJayBee]

In Excel versions prior to 2007 you can create a toolbar icon and assign a
macro to it. When then clicked on it will automatically load in the workbook
containing the macro if it isn't already loaded.

In Excel 2007 the equivalent way is to create an icon in the Quick Access
Toolbar assigning the macro to it. BUT if this icon is clicked on it will
not automatically load the workbook containing the macro if it isn't already
loaded. Instead it gives an error message "Cannot run the macro 'macroname'.
The macro may not be available in this workbook or all macros may be
disabled."
THIS IS A NASTY PROBLEM.
I have a read-only workbook containing a toolset of macros that is shared
across a user base which is migrating from Office 2003 to 2007. The workbook
is in 2003 format (well, 2000 actually). Users just click on their toolbar
icon whenever they want to activate the macros, which I can centrally
maintain remotely.
How do I get this to work in Excel 2007? Is this an Excel 2007 bug?

 

[dgarza]

You can check your Macro security.

Office button > Excel options > Trust Center > Trust Center
Settings...(button) > Macro Settings

Ensure macros are not disabled. We have ours set to "Disable all macros
with notification"

David

 

[Shane Devenshire]

Hi,

Also in the security area you can designate a trusted area (folder) and then
put the file to load in there.

 

[GrayJayBee]

Thanks, but no - security to run Macros is fully enabled. It runs fine if the
workbook containing the macros is pre-opened (but that defeats the whole
purpose of short-cutting it by creating the Quick Access toolbar icon).

 

[GrayJayBee]

Hi Steve, I don't understand your advice. The macro workbook is sitting
read-only on a file share for multiple users to utilise, some on Excel 2003
and some on 2007 (where all these problems are happening).

 

[Bob Berens]

GrayJayBee:

I'm not sure of the implementation, but you might try playing with the
"XLSTART" directory ("x:\Documents and Settings\<username>\Application
Data\Microsoft\Excel\XLSTART") -- .XLx sheets residing there are
automatically loaded whenever Excel starts up. I have two such files in my
XLSTART directory: GLOBAL.XLM and PERSONAL.XLS. They load macros accessible
at any time, from any sheet I'm working on at the time. You might try
sticking a sheet or macro file that loads your desired macros into this
directory, or insert a macro that opens your macro sheet into your users'
GLOBAL.xlm sheet.

 

[GrayJayBee]

Thanks Bob

Hmmm, yes this might be a work-around to this bug (I've decided it MUST be a
bug). Can I re-play your advice to make sure I understand it...

I create a simple empty workbook called MacroBugWorkAround.xlm (say)
containing what is in effect a bootstrap loader macro, which for sake of
argument I'll call RetrieveTools. This macro will first load the read-only
shared workbook that Excel 2007 won't automatically load in when users click
on their current Quick Access Toolbar icon. Then it will transfer control to
the macro the user is trying to run.
I will ask the user to ...
- store MacroBugWorkAround.xlm in XLSTART.
- then restart Excel
- then delete the icon they created in the Quick Access Toolbar that
doesn't work
- then create a fresh icon in the QAT that calls macro RetrieveTools

I realise I'll have to include a check in RetrieveTools to stop it bombing
out if the shared workbook has already been opened.

Is this what you are recommending?
GJB

 

[Bob Berens]

I don't know if this is exactly what you're trying to accomplish, but I just
tried this: I created a new workbook, and recorded a macro that simply typed
"Hello, world" in the current cell; I saved the workbook as a macro-enabled
workbook named (for sake of argument) "ShortcutMacroTest.xlsm" in the "My
Documents" folder.

I then created a shortcut to this workbook in my "C:\Documents and
Settings\rberens\Application Data\Microsoft\Excel\XLSTART" folder
(right-click-and-drag, "create shortcuts here"). Note, I didn't copy the
whole workbook, just a shortcut to it. I closed Excel, then re-opened it,
and it automatically loaded "ShortcutMacroTest.xlsm", but with the "macros
disabled" banner. I "allowed content" and could run the HelloWorld macro on
that sheet and any other open workbook. I then went into the "Excel Options
| Trust Center | Trusted Locations" window and added the "My Documents"
folder to the trusted locations. Re-starting Excel, I could run the macro
without any prompt.

So, maybe all you have to do is copy into your users XLSTART directory a
shortcut to your read-only shared workbook that you can't get Excel to load
automatically, and probably add its directory to their "trusted locations"
list.

Good luck, and let me know how it works out...

:Bob

Thanks Bob

Hmmm, yes this might be a work-around to this bug (I've decided it MUST be a
bug). Can I re-play your advice to make sure I understand it...

<<SNIP>>

 

[GrayJayBee]

Bob

Many thanks, that is a very ingenious way around the bug. It does work,
although I'll have to explain to my users how to locate the XLSTART directory.
I was also able to do it the way I'd suggested, ie by creating an
intermediate loader macro in XLSTART. I automated the setup by using a macro
routine that retrieves the User name then builds the correct full path to
save itself in XLSTART (meaning the user doesn't need to try to find it and
manually move the workbook).
I cannot do this for your approach because I don't know how to write a VB
macro that automates your solution, ie creates a shortcut and sticks it in
XLSTART. But your way is much neater and will work for all my 2000/2003/2007
users I think. Many thanks. PS Do you know of a simple way of automating the
creation of this shortcut and moving it to XLSTART using VB? (All I'd get
them to key in is the path to the shared workbook which the shortcut needs to
be taken from)

GJB