Find if workbook_open exists

[tonywig]

How can I (using VBA) detect whether or not a workbook_open macro exists in a workbook?

I've "guessed" at ...

ThisWorkbook.VBProject.VBComponents("Workbook_Open").Name

... but that doesn't seem to work (error 9 - subscript out of range).

Can anyone help?
Thanks in advance.

[dominicb]

Good afternoon tonywig

Something like this should do the trick :

Sub test()
If ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.CodeName).CodeModule _
 .Find("Private Sub Workbook_Open()", 1, 1, 100, 1000) Then
MsgBox "Workbook module found"
End If
End Sub

HTH

DominicB

[tonywig]

Worked an absolute treat.
Thanks Dominic.

Is there an "easy" way to delete it if it's found?

Thanks again.
T.

[dominicb]

Hi tonywig

Is there an "easy" way to delete it if it's found?

There's two easy ways
(1) Chip Pearson describes how to delete the module as an entity using the VBIDE object in his pages, but you have to set a refrence to the extensibilty library.

(2) Using the code below will delete the lines one at a time without the need to set any references

Sub test()
On Error Resume Next
Set codemod = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.CodeName).CodeModule
NumLines = codemod.ProcCountLines("Workbook_Open", 0)
For n = 1 To NumLines
codemod.DeleteLines 1
Next n
End Sub

HTH

DominicB

[tonywig]

Thankyou so much.
Excellent.
Knew it must be possible but was missing the technical knowledge to achieve it.
Luckily for me, you have that a-plenty.
Thanks again.