Removing auto_open from workbook after execution

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.
..
 
B

Bernie Deitrick

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
 
Top