Disable Auto_Open with VBA

W

wrkoch

I have an auto_open macro I would like to run only once. Ever.

Is there a way to diable this event programmatically? I thought abou
embedding code in the Window_open event to turn off the Auto_Ope
event.....

Yes, I could just set a cell value but I'm looking for ways to do i
without modifying the content of the spreadsheet. I could use
variable to stop the processing I want done but I can't seem to fin
one that remains behind when the workbook/application closes.

Thanks in advance for your help!

Warre
 
W

wrkoch

Didn't work. Threw a 1004 run time error on the SET line

. "Programmatic access to Visual Basic Project is not trusted."

Dim x As Object
Set x = Application.VBE.ActiveVBProject.VBComponent
 
D

Dave Peterson

xl2002 added more security measures.

You can toggle this (user by user) via:
Tools|Macro|Security|Trusted Sources Tab
 
W

wrkoch

Well that wasn't very nice! I can't do that on a corporate deployment.
Too bad -- it was exactly what I wanted to do! I'll have to figure
way to set some sort of variable that will travel with the workbook
 
T

Tom Ogilvy

I assume the Auto_Open performs some type of action. Can't you have it
check if the action has been performed and quit if it has?

If you want the variable approach

Public Sub Auto_open()
Dim nm As Name
On Error Resume Next
Set nm = ThisWorkbook.Names("myflag")
On Error GoTo 0
If Not nm Is Nothing Then
MsgBox "Already run"
Exit Sub
End If
ThisWorkbook.Names.Add Name:="myflag", _
RefersTo:="=1", Visible:=False
MsgBox "Name Created"
' code that does things
End Sub
 
W

wrkoch

Yup -- that's what I'm gonna do! Just started programming that up whe
I got your email. I appreciate the info -- couldn't figure out how t
check for the existence of the name in the collection.

Thanks
 
Top