 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2007Simple way to enable just my personal macros in Excel (& Office) 2007
We recently upgraded to Office 2007. I have a number of macros in Excel. I just opened a spreadsheet I use periodically and I get a #NAME? error in every cell with a macro reference. These are my own personal macros. How do I enable them without opening the doors to potential malware? Thanks -- Hi LurfysMa, > These are my own personal macros. How do I enable them without opening > the doors to potential malware? You can tell Excel 2007 to trust all macros in a specific folder on your drive: - click the Office button and choose Excel Options - Click "Trust Center" and click the "Trust Center Settings" button - Click the "Trusted Locations" tab and click the "Add new location" button to add a new folder as a trusted location. - OK your way back to Excel. Might be needed to restart Excel after this to make it work. Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Member of: Professional Office Developer Association www.proofficedev.com >Hi LurfysMa, > [quoted text clipped - 9 lines] >button to add a new folder as a trusted location. >- OK your way back to Excel. How do I know which folder my VBA macros are in? It's not like a VB project. I just do Alt+F11. There are already a number of folders in that list including: C:\Program Files\Microsoft Office\Templates\ C:\Documents & Settings\Administrator\App Data\MSFT\Excel\XLSTART\ C:\Program Files\Microsoft Office\Office12\XLSTART\ C:\Documents & Settings\Administrator\App Data\MSFT\Templates\ C:\Program Files\Microsoft Office\Office12\STARTUP\ C:\Program Files\Microsoft Office\Office12\LIBRARY\ >Might be needed to restart Excel after this to make it work. > [quoted text clipped - 6 lines] >Professional Office Developer Association >www.proofficedev.com -- Hi LurfysMa, > How do I know which folder my VBA macros are in? It's not like a VB > project. I just do Alt+F11. In Excel, VBA code is a part of a workbook. Excel has a special workbook called "Personal macro workbook", with a filename of Personal.xls (or in Excel 2007 personal.xlsb). This workbook is hidden from the user interface and is stored in a folder called: C:\Documents and settings\YourUserName\Application Data\Microsoft\Excel\XLSTART\ Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Member of: Professional Office Developer Association www.proofficedev.com >Hi LurfysMa, > [quoted text clipped - 9 lines] > C:\Documents and settings\YourUserName\Application >Data\Microsoft\Excel\XLSTART\ There is no such folder. I did a Search (with hidden files included). It returned only: C:\Documents and Settings\Administrator\Recent Using Explorer (with hidden files viewed), I found: C:\Documents and settings\Administrator \Application Data\Microsoft\Excel\XLSTART\ The folder is empty. Not sure why it didn't appear in the Search results. No other \Excel\XLSTART found. Puzzling... What's the name of the file containing the macros? -- Hi type Alt+F11 to open the VBE In the Immediate window type ?application.StartupPath For me it returns C:\Documents and Settings\Roger Govier\Application Data\Microsoft\Excel\XLSTART The file containing your macros will be called Personal.xls unless you have renamed it to something else. In my case, I don't have my personal.xls for 2003 or my personal.xlsb stored there, and I have used registry settings to tell where Excel to find them. If you do not have any success with finding your file and want to go this route,come back again and I will furnish more details.  SignatureRegards Roger Govier >>Hi LurfysMa, >> [quoted text clipped - 31 lines] > > -- >Hi > >type Alt+F11 to open the VBE >In the Immediate window type >?application.StartupPath On the old (Office 2000) system, this returns: C:\Documents and Settings\Administrator \Application Data\Microsoft\Excel\XLSTART On the new (Office 2007) system, I first got an error message saying that macros are disabled. I then went to the Trust Center and enabled all macros, afterwhich I got the same result as above. On the (semi) bright side, the macros now work, but apparently my system is completely unprotected from malware. >For me it returns >C:\Documents and Settings\Roger Govier\Application >Data\Microsoft\Excel\XLSTART > >The file containing your macros will be called Personal.xls unless you >have renamed it to something else. I searched the entire hard disk. There are no files named personal.* at all. >In my case, I don't have my personal.xls for 2003 or my personal.xlsb >stored there, and I have used registry settings to tell where Excel to >find them. If you do not have any success with finding your file and >want to go this route,come back again and I will furnish more details. I would like to keep my system as standard as possible. I'd like it to be a tool -- not a career. ;-) Thanks for the help. -- Hi LurfysMa, > On the (semi) bright side, the macros now work, but apparently my > system is completely unprotected from malware. Well, you can of course set security to a slightly more secure level so that you get a security warning on which you can decide whether or not to enable macros. Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Member of: Professional Office Developer Association www.proofficedev.com Hi LurfysMa, > There is no such folder. If you record a macro and tell Excel to store it in your personalmacro workbook, a file called personal.xlsb is created in that location. Maybe Excel even creates the folder too. Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Member of: Professional Office Developer Association www.proofficedev.com >Hi LurfysMa, > [quoted text clipped - 3 lines] >workbook, a file called personal.xlsb is created in that location. >Maybe Excel even creates the folder too. No such file on either the old (Office 2000) system where the macros work or the new (OFfice 2007) system where they don't unless I enable all macros. -- Hi LurfysMa, > No such file on either the old (Office 2000) system where the macros > work or the new (OFfice 2007) system where they don't unless I enable > all macros. Did you copy the personal.xls from old to new system? If so, you'd best add that folder to your trusted locations list AND add it to the "open all files from" folder. Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Member of: Professional Office Developer Association www.proofficedev.com Hi Jan Karel >add it to the "open all files from" folder I had never noticed that feature in the General section of Advanced options Thanks for drawing my attention to it. SignatureRegards Roger Govier > Hi LurfysMa, > [quoted text clipped - 14 lines] > Professional Office Developer Association > www.proofficedev.com Hi Roger, > >add it to the "open all files from" folder > I had never noticed that feature in the General section of Advanced > options It is one which used to cause frequent posts of people wondering why all of a sudden excel opened up with all their files from the my documents folder (back then, the name of the beast in Tools, options was a bit obscure, something like "alternate startup file location", luring the users to believe that they could use that to set a default folder which would be used on file, open). Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Member of: Professional Office Developer Association www.proofficedev.com Hi Jan Karel, Yes I am familiar with it and its change in description as we have gone through previous XL versions. I thought it had just "disappeared" in XL2007 - like a few other things. Many thanks SignatureRegards Roger Govier > Hi Roger, > [quoted text clipped - 17 lines] > Professional Office Developer Association > www.proofficedev.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.