Removing Macros

[RichP]

Hi, I hope someone can help me.
I've been working on a spreadsheet for months, and had initially recorded a macro to delete the contents of several cells on a page. I no longer need this, so I deleted it with the Macro dialog box, and then went into the VB screen and deleted the module. I saved the book, re-opened it, and it asks whether I want to enable the macros! It now also asks if I want to enable macros when I open any other book. I know that there are many posts about this, but none of the solutions posted have been able to solve this.
With all the books I open, in the VB screen, double clicking on "This Workbook", and looking under Workbook (not General) I get
Private Sub Workbook_Open()
End Sub
which I can't delete. Is this the culprit, or is there something else I should look at?

Thanks for your help,
R.

 

[dk]

If you've got that procedure in the ThisWorkbook code module then yes, that's the culprit.

Excel will show the dialog if any workbook or worksheet code module contains a procedure (even if it doesn't do anything). It will also show it if you have an empty standard module.

Have a look at this link. Near the bottom of the page is a section called Deleting All VBA code in a project.

http://www.cpearson.com/excel/vbe.htm

 

[RichP]

Dan,
it worked a treat - thanks for the link.
Out of curiosit now, if I go back to the VB screen, double click on a sheet, and look under Worksheet, there is code there:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
If I save the document having looked at this (no typing, deleting, etc), the next time I open the book, it sees a macro. Can you tell me why this is? Excuse the dumb question, but I'm new to this VBA stuff.
Thanks again for your help,
R

 

[dk]

Hello,

It's not a dumb question. Basically, when you click on the Worksheet it allows you to create procedure templates for worksheet events. You can choose different events using the right hand drop down box :-

Activate, BeforeDoubleClick, BeforeRightClick, Calculate, Change, Deactivate, FollowHyperlink and SelectionChange.

The default procedure is SelectionChange and that is why it VBA creates a blank procedure.

The macro warning will show up if you have any procedures at all, even if they don't actually contain any code in between this:-


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


and this


End Sub


Hope that helps. Let me know if something's not clear.

 

[RichP]

Dan,
understood, thanks for the answer.
R.