Macro's Disappearing

[NoosaNic]

I would firstly like to apologise if this topic has been discussed. I used the search facility and it came back with 8502 links

I have Excel 2003, am not VBA proficient (understatement) and am trying to do something quite simple. I have never had issues with my macros, but for some reason of late they are disappearing. I have tried saving them in Personal.XLS but they then dissappear again. Some very old ones stay, but any written in the last 6-months are never to be seen again.

Here is what I am attempting to do:

I run a database program that spits out a .dbf file. This file names the worksheet automatically. The file contains 3 columns of numbers going down 20 rows. All I am wanting to do is plot those colums on a simple line chart and then reformat the chart. I understand that the chart can only reference Sheet1. But even if I run two macro's, the first to plot the chart into a new Sheet1 and the second to format it, they still disappear.

Can I:

(1) Plot and format a chart in a worksheet that is automatically named by the .dbf output?
(2) Do so within the original sheet with a single macro
(3) How the heck do I get these macro's to always be available?

Thanks in advance,
Nic

 

[Datsmart]

Where is your Personal.xls file located?
It should be in a folder similar to this:
C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART\
Note that \Application Data\ is a hidden directory and won't show unless you have configured WinExplorer to show hidden folders.

Open the VBA Editor, (Alt-F11). The left side panel called Project Explorer, (Ctrl-R if it is not there), should have a VBA project called "Personal.xls". If not, follow these steps to have Excel create one.
1. Open Tools menu
2. Choose Macro/Record New Macro…
3. "Change the store macro in: combobox to "Personal Macro Workbook"
4. Press OK.
5. Click on new cell.
6. Stop recording.

This will create your Personal.xls module.

 

[NoosaNic]

Thanks John.

There was no Personal file in XLSTART.

I followed the instructions. Still no Personal file in XLSTART, but I did see this one created:

No macro's are saved again. Should I rename this one and place it into XLSTART?

 

[Datsmart]

Do you see Personal.xls in VBA Editor?

 

[NoosaNic]

No. This is all there is...

 

[Datsmart]

Go through the steps again. Pay particular attention to step 3.

Steps to have Excel create the Personal.xls file.
1. Open Tools menu
2. Choose Macro | Record New Macro…
3. Change the "Store macro in:" combobox to "Personal Macro Workbook"
4. Press OK.
5. Click on new cell.
6. Stop recording.

You should see the Personal.xls module in the Project Explorer panel.
If it is still not there, do a search for the "Personal.xls" file in Windows Explorer, be sure to option to search for hidden files. If you find it somewhere else, try moving it to the XLStart folder. Then close and restart Excel.