I have a Workbook_Open macro which is only required when the document is
opened for the first time by a user. I want to have it remove itself
after running. I suspect the answer lies in removing a VBComponent but
I don't know how to tackle this.

Can anyone help? Point me in the right direction?

Thanks.


--
ANDYGM
------------------------------------------------------------------------
ANDYGM's Profile: http://www.excelforum.com/member.php...fo&userid=3452
View this thread: http://www.excelforum.com/showthread...hreadid=532200

'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------

MsgBox "This procedure runs once only"

Dim oCodeModule As Object
Dim iStart As Long
Dim cLines As Long

Set oCodeModule =
ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule
With oCodeModule
iStart = .ProcStartLine("Workbook_Open", 0)
cLines = .ProcCountLines("Workbook_Open", 0)
.DeleteLines iStart, cLines
On Error GoTo 0
Exit Sub
End With


End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ANDYGM" wrote in
message ...

I have a Workbook_Open macro which is only required when the document is
opened for the first time by a user. I want to have it remove itself
after running. I suspect the answer lies in removing a VBComponent but
I don't know how to tackle this.

Can anyone help? Point me in the right direction?

Thanks.


--
ANDYGM
------------------------------------------------------------------------
ANDYGM's Profile:
http://www.excelforum.com/member.php...fo&userid=3452
View this thread: http://www.excelforum.com/showthread...hreadid=532200

Rather than writing complex code to edit code modules, why not just save a
registry value the first time the macro is run successfully, and have the
macro check for this variable when the workbook is opened.

Robin Hammond
www.enhanceddatasystems.com

"ANDYGM" wrote in
message ...

I have a Workbook_Open macro which is only required when the document is
opened for the first time by a user. I want to have it remove itself
after running. I suspect the answer lies in removing a VBComponent but
I don't know how to tackle this.

Can anyone help? Point me in the right direction?

Thanks.


--
ANDYGM
------------------------------------------------------------------------
ANDYGM's Profile:
http://www.excelforum.com/member.php...fo&userid=3452
View this thread: http://www.excelforum.com/showthread...hreadid=532200

Great! I used the code and it worked perfectly. Thanks.


--
ANDYGM
------------------------------------------------------------------------
ANDYGM's Profile: http://www.excelforum.com/member.php...fo&userid=3452
View this thread: http://www.excelforum.com/showthread...hreadid=532200