|
Editing macros
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 |
Editing macros
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 "Dudley" wrote: 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 |
Editing macros
Maybe you also changed ToolsMacroMacros "Macros in:" to a workbook that
has no macros. Mine is generally set to "All open workbooks" Gord Dibben MS Excel MVP On Tue, 13 Jan 2009 09:38:04 -0800, Dudley wrote: 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 |
Editing macros
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" wrote: Maybe you also changed ToolsMacroMacros "Macros in:" to a workbook that has no macros. Mine is generally set to "All open workbooks" Gord Dibben MS Excel MVP On Tue, 13 Jan 2009 09:38:04 -0800, Dudley wrote: 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 |
Editing macros
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 ToolsMacroMacros Gord On Thu, 15 Jan 2009 04:26:09 -0800, Dudley wrote: 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" wrote: Maybe you also changed ToolsMacroMacros "Macros in:" to a workbook that has no macros. Mine is generally set to "All open workbooks" Gord Dibben MS Excel MVP On Tue, 13 Jan 2009 09:38:04 -0800, Dudley wrote: 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 |
Editing macros
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" wrote: 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 ToolsMacroMacros Gord On Thu, 15 Jan 2009 04:26:09 -0800, Dudley wrote: 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" wrote: Maybe you also changed ToolsMacroMacros "Macros in:" to a workbook that has no macros. Mine is generally set to "All open workbooks" Gord Dibben MS Excel MVP On Tue, 13 Jan 2009 09:38:04 -0800, Dudley wrote: 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 |
Editing macros
To create a Personal.xls file go to ToolsMacrosRecord 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 WindowUnhide 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 WindowHide 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 ToolsMacroMacros list. Note: recorded macros must be edited to run on ActiveWorkbook and ActiveSheet rather than hard-coded names Gord On Fri, 16 Jan 2009 06:42:05 -0800, Dudley wrote: 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" wrote: 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 ToolsMacroMacros Gord On Thu, 15 Jan 2009 04:26:09 -0800, Dudley wrote: 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" wrote: Maybe you also changed ToolsMacroMacros "Macros in:" to a workbook that has no macros. Mine is generally set to "All open workbooks" Gord Dibben MS Excel MVP On Tue, 13 Jan 2009 09:38:04 -0800, Dudley wrote: 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 |
Editing macros
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" wrote: To create a Personal.xls file go to ToolsMacrosRecord 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 WindowUnhide 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 WindowHide 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 ToolsMacroMacros list. Note: recorded macros must be edited to run on ActiveWorkbook and ActiveSheet rather than hard-coded names Gord On Fri, 16 Jan 2009 06:42:05 -0800, Dudley wrote: 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" wrote: 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 ToolsMacroMacros Gord On Thu, 15 Jan 2009 04:26:09 -0800, Dudley wrote: 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" wrote: Maybe you also changed ToolsMacroMacros "Macros in:" to a workbook that has no macros. Mine is generally set to "All open workbooks" Gord Dibben MS Excel MVP On Tue, 13 Jan 2009 09:38:04 -0800, Dudley wrote: 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 |
Editing macros
If a button on a Toolbar is running the wrong(old) macro, you have to
re-assign. In ToolsCustomize select the button and "Assign Macro" Change the oldfilename.xls!macro to Personal.xls!macro Close Excel so Excel10.xlb gets updated. Gord On Mon, 19 Jan 2009 09:01:02 -0800, Dudley wrote: 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" wrote: To create a Personal.xls file go to ToolsMacrosRecord 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 WindowUnhide 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 WindowHide 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 ToolsMacroMacros list. Note: recorded macros must be edited to run on ActiveWorkbook and ActiveSheet rather than hard-coded names Gord On Fri, 16 Jan 2009 06:42:05 -0800, Dudley wrote: 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" wrote: 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 ToolsMacroMacros Gord On Thu, 15 Jan 2009 04:26:09 -0800, Dudley wrote: 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" wrote: Maybe you also changed ToolsMacroMacros "Macros in:" to a workbook that has no macros. Mine is generally set to "All open workbooks" Gord Dibben MS Excel MVP On Tue, 13 Jan 2009 09:38:04 -0800, Dudley wrote: 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 |
Editing macros
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 "Gord Dibben" wrote: If a button on a Toolbar is running the wrong(old) macro, you have to re-assign. In ToolsCustomize select the button and "Assign Macro" Change the oldfilename.xls!macro to Personal.xls!macro Close Excel so Excel10.xlb gets updated. Gord On Mon, 19 Jan 2009 09:01:02 -0800, Dudley wrote: 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" wrote: To create a Personal.xls file go to ToolsMacrosRecord 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 WindowUnhide 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 WindowHide 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 ToolsMacroMacros list. Note: recorded macros must be edited to run on ActiveWorkbook and ActiveSheet rather than hard-coded names Gord On Fri, 16 Jan 2009 06:42:05 -0800, Dudley wrote: 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" wrote: 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 ToolsMacroMacros Gord On Thu, 15 Jan 2009 04:26:09 -0800, Dudley wrote: 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" wrote: Maybe you also changed ToolsMacroMacros "Macros in:" to a workbook that has no macros. Mine is generally set to "All open workbooks" Gord Dibben MS Excel MVP On Tue, 13 Jan 2009 09:38:04 -0800, Dudley wrote: 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 |
| All times are GMT +1. The time now is 01:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com