asked on

Disabling macros or deleting code.

I have a template of a workbook which performs data collection/sorting. When this is finished i save the workbook as a .xls using code(the easy bit). However as the template uses the workbook_open function, this executes again when the .xls workbook opens. As I cannot rely on the user to select disable macros, can this be set when saving the workbook or better still can all the modules and code be removed from the .xls.

Code please if it can be done.

Axel

ASKER CERTIFIED SOLUTION

Brian Mulder

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave

I tried something different from your request. The code looks for "Sub auto_open" and renames it "Sub auto_openA" which will stop the code running on startup but you still have the code if you need it later.

Sub changecode()
Dim c As Object
For Each c In ThisWorkbook.VBProject.VBComponents
NumLines = c.CodeModule.CountOfLines
For i = 1 To NumLines
stringA = Trim(c.CodeModule.Lines(i, 1))
If Left(stringA, 15) = "Sub auto_open()" Then ' find the macro that needs to be name changed
Set wbcodemod = ActiveWorkbook.VBProject.VBComponents(c.Name).CodeModule
With wbcodemod
.deletelines i
.insertlines i, "Sub auto_openA" 'change name
End With
Else

End If
Next
Next
End Sub

Sub auto_open()
'do stuff
End Sub

Dave

Doh

(1) I should have refreshed. Sorry Bruintje
(2) I looked for the wrong open event anyhow

Cheers

Dave

Brian Mulder

no worry happens all the time :)