Disable Auto_Open with VBA
 

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

--
Message posted from http://www.ExcelForum.com

Disable Auto_Open with VBA
 

http://support.microsoft.com/default...09&Product=xlw
VBA: How To Delete a Sub Procedure After It Runs Once



--
Regards,
Tom Ogilvy

"wrkoch " wrote in message
...
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 about
embedding code in the Window_open event to turn off the Auto_Open
event.....

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

Thanks in advance for your help!

Warren


---
Message posted from http://www.ExcelForum.com/

Disable Auto_Open with VBA
 

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

--
Message posted from http://www.ExcelForum.com

Disable Auto_Open with VBA
 

xl2002 added more security measures.

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



"wrkoch <" wrote:

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

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Disable Auto_Open with VBA
 

then grant programmatic access to the VBE (I believe).

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
xl2002 added more security measures.

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



"wrkoch <" wrote:

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

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Disable Auto_Open with VBA
 

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

--
Message posted from http://www.ExcelForum.com

Disable Auto_Open with VBA
 

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

--
Regards,
Tom Ogilvy

"wrkoch " wrote in message
...
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 a
way to set some sort of variable that will travel with the workbook.


---
Message posted from http://www.ExcelForum.com/

Disable Auto_Open with VBA
 

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

--
Message posted from http://www.ExcelForum.com