Disabling macros or deleting code.

Posted on 2003-03-27
Medium Priority
Last Modified: 2012-05-04
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.

Question by:ff_axel
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 44

Accepted Solution

bruintje earned 500 total points
ID: 8218378
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, _
         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


LVL 50

Expert Comment

by:Dave Brett
ID: 8218602
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

            End If
End Sub

Sub auto_open()
'do stuff
End Sub
LVL 50

Expert Comment

by:Dave Brett
ID: 8218621

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


LVL 44

Expert Comment

ID: 8218939
no worry happens all the time :)

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question