How do I delete a missing reference to a Macro

[Trevor W]

First, I have no idea how a reference to a macro got installed. I copied a
block of cells from one spreadsheet and pasted into another using paste
special: values.

I almost immediately made a mistake and typed ctrl-z to undo the mistake. I
then get n error that a macro is missing. I look in the Macro manager and two
references to two macros are listed and the original worksheet is from the
spreadsheet where I just copied a small block of data.

My problem: When I highlight the macros, the delete button is greyed-out - I
cannot delete or edit them. My ctrl-z is locked up to these bogus macros.

I am using Office (Excel) 2007 Professional

PLEASE - What do I do?

Who is the %^@* (sorry - I am mad) that programmed this and turned off the
delete button or at least didn't create a delete reference button.

 

[Normek]

Make sure that your sheet is not protected,
Select the 'Data' tab then 'edit links' then break the offending links.

 

[Trevor W]

First, the auto-link sent in my email to bring up this post does not work.

Now, the worksheet is not protected, the workbook is not protected, and
Macro security is enabled.

There is a "bug" - I suspect - where a Macro referenced in another workbook
(where the other workbook is no longer available) cannot be deleted.

Or...

There is just some weird problem. I reallydon't get how the macro was added
to the workbook in the first place. I did not add it. I had created a new
workbook, added some content manually and then copied a range of data and
"pasted special - values".

I then tried to undo a mistake within 15-20 keystokes of pasting the values
and used "ctrl-z" to undo. I then got a message that a macro is no available
which was assigned to "ctrl-z".

I was able to change the hot-key for the macro and free-up "ctrl-z", but I
cannot delete the reference to the macro referenced in a workbook I have
never had access to.

I have been using Excel since the day it was released and am no beginner. I
have found finding where features have been movd in 2007 to be somewhat a
pain. I don't understand the logic behind some of the placement choices, but
anyway...


I have tried every trick I am aware of and it just seems the the programming
logic is caught in an impossible where I need to relink the missing workbook
to delete the macro and cant link the workbook because I never had it and am
fairly certain it is now no longer in existence.

I suppose I can create a dummy macro and try relinking the dummy and see if
I can delete it.

 

[Gord Dibben]

You may clear the reference by deleting your Excelxx.xlb file and letting Excel
build a new one.

I would first just rename the file to prevent Excel from finding it then open
Excel.


Gord Dibben MS Excel MVP