Macro to delete other macros

gnrboyd

Well-known Member
Joined
Jun 20, 2002
Messages
563
I have a template that has 2 macros I use to clean up data. It is a 2 step process so I run macro 1 and then macro 2. I then want to send the file on to someone else but I would like to have the second macro delete the first one as well as itself. (if that is possible) My goal is to not have to have the people I send the data to deal with the security issue of enabling/disabling macros. I did search the site and didn't come up with anything that matches my problem. My Excel level on macros is record and try to tweak so I would like this to be as simple as possible. Thank you for your help.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
I found this link in another post but I am afraid I am not familair enough with the visual basic structure to know what I am doing. I have a couple of simple macros I use in all workbooks and want to make sure that those do not get deleted. I only want to delete the ones that are within my workbook. I named them when I recorded them but they say module 1 and module 4 when I step inside the macro.
 
Upvote 0
In order to help you, I need to know:

1) where you want to store the macro which deletes other macros,
2) the name of the macros you want delete,
3) the name of the code module containing the macros you want delete (you can see it from the title of VBE window. on the top left side)
 
Upvote 0
Sorry.. It's important the name of the workbook containing the macros you want delete
 
Upvote 0
This code will delete ALL code in sheet3 in the workbook named You will need to change these to suite Your needs

Jack

code:
Code:
Option Explicit

Sub JR_DeleteCodeInModules_VBE()

'written by: Jack in the UK
'www.excel-it.com
'excel Xp+

Dim WorkBook_VBE_CodeModule As CodeModule

Set WorkBook_VBE_CodeModule = Workbooks("treeView jiuk v1.xls").VBProject.VBComponents("Sheet3").CodeModule

WorkBook_VBE_CodeModule.DeleteLines 1, WorkBook_VBE_CodeModule.CountOfLines
 
theEnd:
'// jiuk - do something

Set WorkBook_VBE_CodeModule = Nothing

Exit Sub
End Sub
 
Upvote 0
Sorry for the delay in responding...

1. I store the macro in the workbook only which is a template named statement.xlt.
2. I want to delete 2 macros when I am finshed with the template. They are named DATE_SORT and Finalize.
3. The code modules say Module 1 and Module 4. (I'm not sure how to rename them to something unique. I've never renamed them in any of my recorded macros so I assume I have many out there with Module 1, 2, etc. Is this bad?)

Thename of the workbook is statement.xlt but it shows statement1 since it is a template and has not been saved/renamed to a new file yet.

This template is used to clean up data and then forward to other users. I don't want the other users to have to deal with the security warning that macros give unless the security settings have been altered to low)

Thank you


Sorry.. It's important the name of the workbook containing the macros you want delete

chiello Posted: 12 Feb 2005 00:02 Post subject:

--------------------------------------------------------------------------------

In order to help you, I need to know:

1) where you want to store the macro which deletes other macros,
2) the name of the macros you want delete,
3) the name of the code module containing the macros you want delete (you can see it from the title of VBE window. on the top left side)
 
Upvote 0
My goal is to not have to have the people I send the data to deal with the security issue of enabling/disabling macros.

So, if I understand, you want your "cleaned" file to be free from any code (you want to delete all VBA code, in this case 2 macros).

Try the following:

1) Set a reference in VBA to the VBA Extensibility library (see link in previous post to see howto).

2) copy the following code in a standard module of "template.xls"

Sub DeleteAllVBAinFile()
'
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
End Sub



3) Run the macro

4) "Manually" save the workbook (save or saveas...)

Does it work???
 
Upvote 0

Forum statistics

Threads
1,203,490
Messages
6,055,725
Members
444,814
Latest member
AutomateDifficulty

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