Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

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
0
ff_axel
Asked:
ff_axel
  • 2
  • 2
1 Solution
 
bruintjeCommented:
Hello ff_Axel,

you could use this sub

Sub DeleteAllVBA(ByVal wbk As Workbook)

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = wbk.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

and call it like

sub test
  Dim wbk as workbook
  set wbk = Workbooks("MyWorkbook")
  call DeleteAllVBA(wbk)
end sub

HAGD:O)Bruintje

0
 
Dave BrettVice President - Business EvaluationCommented:
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
0
 
Dave BrettVice President - Business EvaluationCommented:
Doh

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

Cheers

Dave
0
 
bruintjeCommented:
no worry happens all the time :)
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now