Strange Personal.xls behavior or PEBCAK (Excel 2003 on XP Pro)

Topic

New Reply

I recently received a new pc at work (repaired machine) and had to transfer everything from the old.
I checked Excel help for the proper location of the macro file: Personal.xls and put it where it said to.
Personal.xls is visible to the VB Editor, but not to the Macros dialogue box and I just can’t figure this out!

Any help or ideas gratefully accepted!

Thanks,

Dan

Reply | Quote

Replies

Try recording a short macro and specify that it should be stored in your personal macro workbook.
Does the recorded macro turn up in the macro list?
Does it turn up in the Visual Basic Editor?

Reply | Quote

Hi, Hans.

Thanks for your interest. I tried that (new macro stored in Personal Macros WB.)
It did not show up in the macro list, nor in the VBE.

Dan

Reply | Quote

I suspect that your Personal.xls has become read-only. Try the following:
– Quit all instances of Excel.
– Locate Personal.xls in Windows Explorer.
– Right-click the file.
– Is the Read-only check box ticked? If so, clear this check box and click OK.
– Start Excel to see if the macros are visible now.

Reply | Quote

I checked that and, no, it isn’t set to be Read-only.
I also checked if the file was set as Hidden (files in XLSTART are set to Hidden will be open and available each time Excel is started.)
Now it is not set as Hidden and the file is invisible to Excel, but it IS visible to the VBE. When I change it to Hidden it is invisible to both.

Dan

Reply | Quote

You shouldn’t mark Personal.xls as hidden in Windows Explorer.

Try the following:
– Quit all instances of Excel.
– Move Personal.xls out of XLSTART.
– Start Excel. There should be no Personal.xls in the Visual Basic Editor.
– Record a small macro stored in your Personal Macro Workbook.
– When you stop recording, do you see the macro in Tools | Macro | Macros? And is there a Personal.xls in the Visual Basic Editor now?

Reply | Quote

Hans,

Appologies for not getting back to you sooner, my pc has been taken over by the IT guys at my company.
They are, on there own independent decision, reinstalling Office.

that may well solve this thing. If it doesn’t I will try your last suggestion and either way I will let you know what happened.
As it is about 15 mins from my departure time from work, it will probably be tomorrow before I can get back to you.

Thanks for your help, so far and , cheers!

Dan

Reply | Quote

I’m baaaack!

Well, it’s a new day, I have a new install of Office 2003 Pro and the same problem as yesterday.

I followed your (Hans’) suggestion and, with the new installation recording a new macro and saving it to Personal Macro file actually worked, and the new macro
showed up in the macro list and in the VBE. I saved the new Personal.xls file and exited Excel and then opened Win. Explorer and searched for the new file
and it couldn’t be found. When I reopened Excel there was no macro to be found.

I then located the earlier Personal.xls file (the one I want to use, with all my macros in it) and double-clicked it to open it in Excel, and got the “macro warning” dialogbox and chose to make the macros available.
Excel showed nothing whatsoever, no opened file, no title in the title bar, and the Hide/Unhide option on the Windows menu was greyed out. Nothing in the macros list, and Personal.xls was visible in the VBE.

By the way, I do have admin rights (locally) on this machine.

(I’m baffled…)

Dan

Reply | Quote

Start Excel.
1) Select Tools | Macro | Security…
Temporarily set macro security to Low.
Quit and restart Excel.
Does this make a difference?
You should reset macro security to Medium.

2) Select Help | About Microsoft Office Excel…
Click the Disabled Items button.
Anything listed there?

Reply | Quote

Hans,

I set security to low and restarted, no change in behavior.

I checked Disabled Items and there were none…

Dan

Reply | Quote

If you record a small macro in your personal macro workbook, does the Window | Unhide menu item become enabled, and does it list Personal.xls?
If so, unhide it, then save it. Don’t quit Excel yet.
Open Windows Explorer. Can you find Personal.xls?
Switch back to Excel and quit.
If you found Personal.xls before quitting Excel, is it still there or did it disappear?

Reply | Quote

Hans,

Sorry for the delay in getting back to you, but sometimes the job has a way of interfering with what I am trying to do here at work!

So, I did your latest suggestion.

First I went through all folders called XLSTART and emptied them-moved whatever was there to another folder.
then I followed your steps.

I found an new XLSTART folder, in a new location that held the new Personal.xls file and I found in one of the original XLSTART folders (that I just emptied) the older Personal.xls files was back!

While writing this I paused, reopened xl and the Personal.xls file opened (visible) so I hid it and closed xl, which prompted me “Do you wish to save the changes to …” which I did. I then opened xl again and it opened with no visible files, but Unhide was active and in selecting it, I was able to see Personal.xls available to be unhid.

I’m thinking, next I should try replacing the new Pers file with the one I want, in this new XLSTART folder and see if that works.

Dan

Reply | Quote

Yes, that sounds like a good idea, but I’d move the one that works now to another folder first.
If the old one doesn’t work, you can rename it, restore the new one to the “working” XLSTART folder and copy the modules from the old one to the new one in the Visual Basic Editor (simply drag the modules).

Reply | Quote

Hallelujah!!

It is working!!

phew. thank you, Hans. I so appreciate your taking the time, having the persistence and seeing me through this.

You are a star!

Dan

Reply | Quote

I’m glad it works too! This kind of problem can be hard to solve…

Reply | Quote