Macro to delete macros

Eric Hearn

New Member
Joined
Mar 17, 2006
Messages
42
I have a workbook sheet containing 5 command buttons, one of which copies the sheet to a new workbook (say Book2) in which it then converts all formulas to values, removes all data validation and deletes the 5 command buttons. However the code which was attached to those buttons remains and gives rise to a macro security warning whenever Book2 is re-opened.
Can I modify the button's macro to also delete all 5 CommandButtonx_Click() macros from Book2?

T.I.A.

Eric Hearn
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,913
Office Version
  1. 365
Platform
  1. Windows
Eric

You can check this out Programming to the VBE.

But why not try looking at a solution that involves not copying the code in the first place?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
I have a workbook sheet containing 5 command buttons, one of which copies the sheet to a new workbook (say Book2) in which it then converts all formulas to values, removes all data validation and deletes the 5 command buttons. However the code which was attached to those buttons remains and gives rise to a macro security warning whenever Book2 is re-opened.
Can I modify the button's macro to also delete all 5 CommandButtonx_Click() macros from Book2?

T.I.A.

Eric Hearn
You can indeed delete the sheetcode using Chip Pearsons code.
Another route would be to create your buttons using the FORMS-toolbar. The macros won't reside in the sheetcode but in "normal" modules.

kind regards,
Erik
 

Eric Hearn

New Member
Joined
Mar 17, 2006
Messages
42
Macro to delete a macro

Hi

Thanks for the leads - unfortunately I'm still puzzled.

I should perhaps add that:-

1. 'Book1' is used repeatedly, and

2. My VBA experience is pretty much limited to recording macros and using VBA help if they don't do what I want!

I tried using the code to delete all VBA from a project, as that's what I want to do, but although it ran without error it didn't appear to do anything.
Also, enabling VBA extensibility appears to need resetting every time. If so, can that be achieved with a macro?

Any further help greatly appreciated.

Eric Hearn
 

Eric Hearn

New Member
Joined
Mar 17, 2006
Messages
42

ADVERTISEMENT

Macro to delete a macro

Hi Erik

I appreciate what you say but that still leaves the problem of getting rid of the unwanted code. (See my reply to Norio)

Any further ideas would be welcome

Regards

Eric Hearn



We all have to start at the beginning and work our way towards the end. Some make it further than others.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Re: Macro to delete a macro

I appreciate what you say but that still leaves the problem of getting rid of the unwanted code. (See my reply to Norio)
I missed this reply today, sorry
you were talking about copying sheets
only the sheetcode will "follow" the sheet not the code residing in other modules
so my reply tried to explain that you can use other kinda buttons so the code wouldn't be in the sheetmodules anymore: so you won't need to remove code

Also, enabling VBA extensibility appears to need resetting every time. If so, can that be achieved with a macro?
when you set the reference in a workbook, it will stay once and for all: perhaps you mean something else ? please explain

setting the reference by code can be done this way

Code:
Sub ActivateVBE6EXT_OLB()

    Dim R
    For Each R In ActiveWorkbook.VBProject.References
    If R.GUID = "{0002E157-0000-0000-C000-000000000046}" Then Exit Sub
    Next

    On Error GoTo NotFound
    ActiveWorkbook.VBProject.References.AddFromGuid _
            GUID:="{0002E157-0000-0000-C000-000000000046}", _
            Major:=5, Minor:=3

    Exit Sub

NotFound:
    MsgBox "CAN'T RUN THIS CODE" & vbCrLf & vbCrLf & _
            "VBE6EXT.OLB IS NOT ON THIS COMPUTER"

End Sub
see
http://groups.google.com/group/micr...&oe=UTF-8&c2coff=1&scoring=d#9d5a07361150f91f

best regards,
Erik
 

Eric Hearn

New Member
Joined
Mar 17, 2006
Messages
42
Hi Erik

Thanks for the further explanation.

I now see exactly what you mean and, of course, it works perfectly!

Thanks again

Best Wishes

Eric Hearn
 

Watch MrExcel Video

Forum statistics

Threads
1,113,846
Messages
5,544,634
Members
410,626
Latest member
rkmadasu
Top