Removing auto_open from workbook after execution

  • Thread starter Thread starter Panos
  • Start date Start date
P

Panos

Hi, is there a way to stop or delete an auto_open macro
from a workbook after it has been executed. I don't want
to change the Macro security settings of my Excel because
the Workbook is opened by many users and not only by me.
..
 
Panos,

1) For the workbook open event (which is the same as the auto open macro,
just in the Thisworkbook's codemodule)
2) if it is the only thing in the codemodule - otherwise, you will need to
change the .DeleteLines parameters
3) this requires a reference to the MS VB extensiblity

Private Sub Workbook_Open()
Dim myBook As Workbook
Dim myVBA As VBIDE.VBComponent

Set myBook = ActiveWorkbook
Set myVBA = myBook.VBProject.VBComponents(myBook.CodeName)

'Do other stuff here

'This deletese itself
With myVBA.CodeModule
.DeleteLines 1, .CountOfLines
End With

End Sub

HTH,
Bernie
MS Excel MVP
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SOS! how do I run Excel without running the VBA in AUTO_OPEN? 1
Remove auto_open macro from Workbook after execution 1
Private Sub Workbook_Open() doesn't run 4
Should be able to change name for a macro 1
Shift key doesn't stop Auto_Open() from firing 1
Help with 'Auto_Open' please... 3
Bypass Auto_Open 13
with a macro open a workbook without the Auto_Open running 3

Back
Top