Editing macros

[Dudley]

I have macros on a toolbar in Excel 2002 and I want to change one of them. I
do this by going into Macros, selecting the one I want to change, and
clicking 'Edit', but now the list of macros has disappeared. Can anyone
advise how I can get the list back? The macros are still working.

Thanks
Dudley

 

[FSt1]

hi
press Alt+F11. this will bring up the vb editor. in the project pane(far
left), expand the project(file) that contains the macros.

Regards
FSt1

 

[Gord Dibben]

Maybe you also changed Tools>Macro>Macros "Macros in:" to a workbook that
has no macros.

Mine is generally set to "All open workbooks"


Gord Dibben MS Excel MVP

 

[Dudley]

Thanks to both of you for your advice. I do use 'All open workbooks'.

I did Alt/F11 and found some of the macros with difficulty, but the one I
wanted was not there. I finally found it when I tried to run it and it gave
an error message and allowed me to edit it.

It seems that macros are linked to specified workbooks, presumably the one
open when I created the macro, and I can only view the code when that
workbook is open, although previously I have viewed all macros to edit them
without any problem.

Dudley

 

[Gord Dibben]

Workbooks can live in the the workbook in which they are created.

If that workbook is not open you won't see the macros.

Or macros can be in your Personal.xls, in which case they would be seen no
matter which workbook(s) are open.

Maybe you previously had a Personal.xls but now have not?

The macros could also be in an Add-in.

In that case they are never visible in Tools>Macro>Macros


Gord

 

[Dudley]

Thanks for your advice.

I cannot find Personal.xls, although presumably I had one before as I could
see all the macros. I have tried creating it but this did not help. Is it
possible to put the existing macros in Personal.xls so that I can see them?

Thanks
Dudley

 

[Gord Dibben]

To create a Personal.xls file go to Tools>Macros>Record New Macro and "Store
Macro In". Select Personal Macro Workbook.

Record some simple steps like copy/paste some data.

Your Personal.xls will be created and when saved will be stored in the
default Office location.

Good idea to Save Personal.xls at this point. You will find it under
Window>Unhide

Save it and Excel will point it to the proper location.

Generally this is the location.

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

Now, if you have existing macros in other workbooks, you can copy them into
a module or modules of Personal.xls by hitting Alt + F11 to go to Visual
Basic Editor, CTRL + r to open Project Explorer.

Select Personal.xls and you can open the module1 containing your recently
recorded macro.

Add macros by copying them from other books(assuming you have some)

Go back to Excel Window by Alt + q

Window>Hide your Personal.xls again then close Excel.

You will be given a message asking to save changes to Personal.xls

Click yes so it opens hidden when next you start Excel

Your macros will be available to all open workbooks from now on.

It will be up to you to devise a method of running them from a button,
shortcut key or by just running from Tools>Macro>Macros list.

Note: recorded macros must be edited to run on ActiveWorkbook and
ActiveSheet rather than hard-coded names



Gord

 

[Dudley]

Thanks very much for your full and helpful instructions.

I still have one problem, but I have founda way round it. I have macros on a
toolbar, and when I edit the copy in personal.xls, the toolbar still runs the
old copy. However, running the macro so to to throw up a runtime error, such
as with the cursor in the top left hand corner, allows me to edit the copy of
the macro which is on the toolbar.

Dudley

 

[Gord Dibben]

If a button on a Toolbar is running the wrong(old) macro, you have to
re-assign.

In Tools>Customize select the button and "Assign Macro"

Change the oldfilename.xls!macro to Personal.xls!macro

Close Excel so Excel10.xlb gets updated.


Gord

 

[Dudley]

Thanks very much but I cannot get this to work.

If I change macro DEPRECIATION to PERSONAL.XLS!DEPRECIATION and try to run
the macro I get a message 'PERSONAL.XLS!DEPRECIATION' not found.

I have also tried changing it to 'C:\Documents and Settings\DMCS\Application
Data\Microsoft\XLStart\PERSONAL.XLS'!DEPRECIATION, but then I get a message,
when I try to run the macro, that two copies of PERSONAL.XLS are open, even
though I only have one.

Thanks
Dudley