Check if Workbook_Open is used in workbook

Question

I have build a custom excel add-in and I want to check if any workbook which is opened has Workbook_Open event handled already in the workbook. Based on it if already handled I want to prompt the user with some instructions to follow. Below is the code snippet of the method used in ThisWorkbookk of user workbook.

    Private Sub Workbook_Open()
    'Some code user had already written here
    End Sub

I already checked ThisWorkbook object, it does not have any property or list of events which are already used.

Is there any way or property to determine if the Open event of workbook is used in User workbook.

Mikku Mikku · Accepted Answer · 2019-07-11T10:16:36

Below is the code that can Check if the WorkBook_Open Procedure exists in a Workbook.

Change the File Name as It is in the Code

set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 Library

Sub Check()

Dim pk As vbext_ProcKind
Dim vbProj As VBIDE.VBProject
Dim vbComp As VBIDE.VBComponent
Dim vbMod As VBIDE.CodeModule

Set app = Excel.Application

For Each vbProj In app.VBE.VBProjects

    If vbProj.filename = "C:\Users\mohit.bansal\Desktop\Test\Faster.xlsm" Then

    For Each vbComp In vbProj.VBComponents

        Set vbMod = vbComp.CodeModule

            iLine = 1
            Do While iLine < vbMod.CountOfLines
              sProcName = vbMod.ProcOfLine(iLine, pk)
              If sProcName <> "" Then
                'Debug.Print vbComp.Name & ": " & sProcName

                If sProcName = "Workbook_Open" Then MsgBox "WorkBook_Open Exists!!"

                iLine = iLine + vbMod.ProcCountLines(sProcName, pk)
              Else
                iLine = iLine + 1
              End If
            Loop

    Next

    End If

Next

End Sub

Courtesy: peltiertech

Check if Workbook_Open is used in workbook

1 Answers