PDA

View Full Version : How to get rid of these dead macros?



musicgold
04-26-2011, 12:43 PM
Hi,

Please see the attached file. This file has some dead macros which I have been trying to remove. This is just one sheet from a large file. The large file is there in my department for years.
As you will notice that there is no code module in the file and nor is there a way to view these macros. The only way to see them in Excel is by using Tools à Macro à Macros…

I want these macros out has someone as assigned the typical keyboard shortcuts to these macros and they are hampering my work style. For example, Ctr + S has been assigned to Macro 5. As a result I can’t use Ctr+S to save my work and that is very annoying. I tried to re-assign the shortcuts but it didn’t work.

I also tried the code given on this page (http://www.exceltip.com/show_tip/Modules,_Class_Modules_in_VBA/Delete_all_macros_in_a_workbook/document_using_VBA_in_Microsoft_Excel/505.html), but even it didn’t work and I got the following message: the VBProject is protected or has no components.

Is there a way to get rid of these macros or at least re-assign the shortcut keys?

Thanks.

Bob Phillips
04-26-2011, 12:55 PM
I think the macros are in hidden sheets that we are not seeing, maybe XLM sheets.

musicgold
04-26-2011, 12:59 PM
Thanks. But, I just copied this one sheet from the main file into this new workbook.

Also, how do I see the XLM sheets?

Bob Phillips
04-26-2011, 01:08 PM
I am suggesting post the whole workbook.

Paul_Hossler
04-26-2011, 01:50 PM
Control-F3 to edit named ranges

Click the first one, scroll to the end of the (very long) list and shift - click the last to select them all

Click Delete

Control-S seems to save the WB. I didn't test any other

Paul

musicgold
04-26-2011, 04:14 PM
Thanks folks.


Control-S seems to save the WB
Yes! What a relief.:clap:


I am suggesting post the whole workbook.
I can't post the whole file for privacy reasons. But I don't understand one thing here. I just copied the data sheet in a new workbook using the 'move or copy' method and the macros still got transferred to the new file. That means those macros are somehow connectd to that sheet. It beats me.:dunno

Paul_Hossler
04-26-2011, 04:48 PM
if you look at the names, there's a Macro03 with a Refers to =Data!Macro03

I deleted all the names from the WB you posted, copied Data to new WB, and there were no macros in the copy

Paul

Jan Karel Pieterse
04-27-2011, 12:12 AM
Note that there is also a number of hidden range names in your file which are corrupt (VBA cannot remove them).

Aussiebear
04-27-2011, 01:03 AM
I wasn't aware you could hide range names. How is this done and naturally how do you locate them?

Jan Karel Pieterse
04-27-2011, 01:24 AM
It is done by VBA code. I located them using my Name Manager add-in (http://www.jkp-ads.com/officemarketplacenm-en.asp)

Aussiebear
04-27-2011, 02:34 AM
I have found 10 macros.... is that all or have you found more?

Jan Karel Pieterse
04-29-2011, 05:17 AM
That was all indeed.