CODEWITHSUNDEEP
excelvb.netevent-handling
SNasrZ
SNasrZ

Reputation: 11

CreateEventProc has crash on vb.net

I want to add an event to a sheet in Excel using VB.net I've written the following code for this But I encountered the problem in the Specified line

Please help me

this program has crashed on line 22

Imports Microsoft.Office.Interop
Imports Microsoft.Vbe.Interop
Imports System.Runtime.InteropServices
Imports System.Text

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim excelApplication As New Excel.Application
        Dim excelWorkbooks As Excel.Workbooks = excelApplication.Workbooks
        Dim excelWorkbook As Excel.Workbook = excelWorkbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
        Dim excelWorkSheet As Excel.Worksheet = excelWorkbook.ActiveSheet

        excelApplication.Visible = True
        Dim VBComp As Microsoft.Vbe.Interop.VBComponent
        Dim CodeMod As Microsoft.Vbe.Interop.CodeModule
        Dim LineNum As Long

        VBComp = excelApplication.ActiveWorkbook.VBProject.VBComponents(excelWorkSheet.Index)
        CodeMod = VBComp.CodeModule
        With CodeMod
            LineNum = .CreateEventProc("Active", "Worksheet")'**Crash: Event handler is invalid**
            LineNum = LineNum + 1 : .InsertLines(LineNum, " ")
            LineNum = LineNum + 1 : .InsertLines(LineNum, "Msgbox(""Hi"")")
        End With
        'Save and quit
        excelWorkbook.SaveAs("F:\MyVBAExcelFile.xlsm", Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled)

        excelApplication.Quit()

        Marshal.ReleaseComObject(excelWorkbooks)
        Marshal.ReleaseComObject(excelWorkbook)
        Marshal.ReleaseComObject(excelApplication)

    End Sub

End Class

Upvotes: 0

Views: 76

Answers (1)

David Sdot
David Sdot

Reputation: 2333

Active is a state not an event, you should try Activate to make it work. The error is thrown when an event does not exist (like Active).

Upvotes: 1

Related Questions