Personal Macros & XLStart Missing

[Jim G]

I'm using XL2007 in Vista (big mistake). I have lost my personal macro book.
When I look for it in the Users\ME\Appdata\local\Microsoft\etc not only is
it not there but the XLStart directory is missing. I decided to record a new
one in XL only to be greeted with "Personal macro work book in the startup
directory must stay open for recording". Obviously it never loaded.

I have to restart Vista several times a day due to network lockups. XL
locks up alo if it is open. The restart is usually a power off rather than
an orderly shutdown. This is the first time I'e lost a file.

Anyone any ideas?

 

[Shane Devenshire]

Hi,

I believe when you upgrade to VISTA, the operating system store the files
previously on your machine. But it might be better to post a question of
that type to the Windows newsgroups.

If you still have Excel 2003 on your computer you can choose
Help>About MS Excel>Disabled Items.

Personal.xls listed?

Enable it.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Jim G]

It was a new vista and office 2007 pre-installed on a new laptop.

However, I found the file in
Users\Me\Appdata\Roaming\Microsoft\Excel\XLSTART. It only showed up after an
advanced search (a VIsta phenomenon).

I launched the file and was then able to save a new macro in XL2007. It
must have been due to the forced crash(s). Although it doesn't explain why
XL couldn't find it, but it opened manually. That's the world of MS we love
so much!

Thanks for the reply.

 

[Dave Peterson]

Excel may think that your personal.xls is corrupted and so excel disabled it.

In xl2003, I can use:
Help|About MS Excel|and click the disabled items button.

In xl2007, I can use:
Office Button|Excel Options|Add-Ins Category|Manage Dropdown|Disabled Items

If the file is really corrupted, you may have to delete this file and replace
the file with one of your backup files.

 

[Jim G]

Opening it manually (after eventually finding it), Excel now loads it
automatically. Thanks.

I have more than one profile and several inexperienced users. Is there a
way to turn the personal macro into an addin to make it available to each
profile and or distribute it to otehr users?

 

[Dave Peterson]

First, don't use the name personal.*. If any of the users have their own
version with the same name, then they'll have to make a decision -- which one
should be used.

I'd save it as an addin with the name it JimGUtils.xla.

Then place it in a common network drive and tell the users to use Tools|addins
to browse for that drive/folder and install it from there.

If the users are not always connected to that common network drive, then I would
distribute the addin with the instructions to save it in a folder with a common
name:

C:\xlUtils\jimgutils.xla

Then the users can use the same tools|addins and browse for this file.

By using a common name, you'll avoid any problem with User defined functions.
(The links to the file will always be the same.)

Opening it manually (after eventually finding it), Excel now loads it
automatically. Thanks.

I have more than one profile and several inexperienced users. Is there a
way to turn the personal macro into an addin to make it available to each
profile and or distribute it to otehr users?

 

[Jim G]

I'll feel stupid (again) if this is obvious.

I've loaded the addin on a test PC with XL2007. I can see that the code is
available if I wanted to copy/use it in a normal macro. Is this the extent
of its usefullness? Can it not be added to buttons or made available/visible
as a macro to run as in the normal case?

I really want inexperienced users to be able to run 'helper' macros to
manipulate or modify data on some system generated reports.

 

[Dave Peterson]

You can't run the macros directly from personal.xls (via alt-f8)?

Double check to see if macros are enabled.

If you're looking for a way to let the users run the macros...

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

I'll feel stupid (again) if this is obvious.

I've loaded the addin on a test PC with XL2007. I can see that the code is
available if I wanted to copy/use it in a normal macro. Is this the extent
of its usefullness? Can it not be added to buttons or made available/visible
as a macro to run as in the normal case?

I really want inexperienced users to be able to run 'helper' macros to
manipulate or modify data on some system generated reports.

 

[Jim G]

Thanks Dave.

I checked all of the links (although Ron's 2007 addin might be a bit
advanced for me). I can see a lot of uses for these and have started
practising with Yours and Ron's. What a clever lad you are!

When I tried the same thing with my Personal macro wookbook (saved as an
addin), it didn't even activate the Addin tab on the ribbon as yours did, so
I don't know what I did wrong. However, I can see my addin macros in the
Excel Options\Customise\Commands\Macros but not using the normal Macro Menu
(alt F8 isn't available in 2007). From there I can add them to the Quick
Access toolbar.

Can the hieght of the addin toolbar be made lower? It's the same size as
the normal toolbars in the ribbon but without the icons.

I have users with 97-2003 and 2007 so I'll play around to see which I like
best in each. Excel can be fun, thanks again for your help.

Cheers

 

[Dave Peterson]

I don't use xl2007 enough to know if (or how) that addin toolbar can change
height.

Maybe someone else will chime in.

Thanks Dave.

I checked all of the links (although Ron's 2007 addin might be a bit
advanced for me). I can see a lot of uses for these and have started
practising with Yours and Ron's. What a clever lad you are!

When I tried the same thing with my Personal macro wookbook (saved as an
addin), it didn't even activate the Addin tab on the ribbon as yours did, so
I don't know what I did wrong. However, I can see my addin macros in the
Excel Options\Customise\Commands\Macros but not using the normal Macro Menu
(alt F8 isn't available in 2007). From there I can add them to the Quick
Access toolbar.

Can the hieght of the addin toolbar be made lower? It's the same size as
the normal toolbars in the ribbon but without the icons.

I have users with 97-2003 and 2007 so I'll play around to see which I like
best in each. Excel can be fun, thanks again for your help.

Cheers


--
Jim

You can't run the macros directly from personal.xls (via alt-f8)?

Double check to see if macros are enabled.

If you're looking for a way to let the users run the macros...

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm