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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top