Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem (bug?) in the way Excel 2007 doesn't automatically load ma
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem (bug?) in the way Excel 2007 doesn't automatically load ma
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 "GrayJayBee" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem (bug?) in the way Excel 2007 doesn't automatically load ma
Hi,
Also in the security area you can designate a trusted area (folder) and then put the file to load in there. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "GrayJayBee" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem (bug?) in the way Excel 2007 doesn't automatically loa
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). "dgarza" wrote: 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 "GrayJayBee" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem (bug?) in the way Excel 2007 doesn't automatically loa
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). "Shane Devenshire" wrote: Hi, Also in the security area you can designate a trusted area (folder) and then put the file to load in there. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "GrayJayBee" wrote: 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem (bug?) in the way Excel 2007 doesn't automatically load ma
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem (bug?) in the way Excel 2007 doesn't automatically loa
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" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem (bug?) in the way Excel 2007 doesn't automatically loa
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 "GrayJayBee" wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem (bug?) in the way Excel 2007 doesn't automatically loa
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 "Bob Berens" wrote: 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 "GrayJayBee" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - Could not load an object because it is not available | Excel Discussion (Misc queries) | |||
cannot get my excel personal macro workbook to load automatically | Excel Discussion (Misc queries) | |||
Excel 2007 automatically redirects links - Linking Problem #2!! | Links and Linking in Excel | |||
Get Started Tab for Excel 2007 failed to load, | Excel Discussion (Misc queries) | |||
Excel 2007: very slow to load spreadsheet | Excel Discussion (Misc queries) |