How to Remove Workbook_Open macro?

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.

'-----------------------------------------------------------------
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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.