Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CreateEventProc for OLE button crashes Excel
I have been having crashes and finally narrowed down the problem to
something specific enough to post the question. The procedure below is intended to be called from a menu choice I've added to the Excel tool bar. It adds a worksheet with a button, and clicking the button is supposed to do something, here just throw up a message box. It works fine if I execute it in Debug by placing the cursor at the end and doing ctrl-F8 (run to cusor). The sheet is added and button works. However, If I execute it from the menu choice on the tool bar it crashes Excel immediately upon return from the call to BuildWorksheet. To be more specific, in the code for a form that gets invoked by the menu choice there is: .... Call BuildWorksheet MsgBox ("Ret from BuilsWorksheet") ..... The message gets printed, and it crashes. Sometimes it even corrupts the Windows kernel so I have to reboot and suffer through a disk scan. To further narrow the problem, I have discovered that it will not crash if I omit the code where the CodeModule is modified (see "Create the code" at the end). Any suggestions greatfully received. TIA Ed Public Sub BuildWorksheet() Dim WSName As String Dim WSType As String WSName = "xyz" WSType = "type1" Dim wks As Worksheet Dim Present As Boolean Present = False For Each wks In Worksheets If wks.name = WSName Then Present = True Exit For End If Next wks If Present Then ActiveWorkbook.Worksheets(WSName).Delete ActiveWorkbook.Sheets.Add Type:=xlWorksheet Debug.Print ActiveWorkbook.ActiveSheet.CodeName ActiveWorkbook.ActiveSheet.name = WSName Names.Add name:=WSName & "!WSType", RefersTo:=WSType Dim WS As Worksheet Dim Btn As OLEObject Set WS = ActiveWorkbook.ActiveSheet ' Create The Button Set Btn = WS.OLEObjects.Add(ClassType:="Forms.CommandButton. 1", _ Left:=WS.Range("D3").Left, Top:=WS.Range("D3").Top, _ Width:=95, Height:=40) Btn.Object.Caption = "Calculate " & WSType ' Create the code Dim StartLine As Long With ActiveWorkbook.VBProject.VBComponents(WS.CodeName) .CodeModule StartLine = .CreateEventProc("Click", Btn.name) + 1 .InsertLines StartLine, " MsgBox(" & Chr(34) & "calc here" & Chr(34) & ")" End With End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Crashes Whenever Add-In Button is Clicked | Setting up and Configuration of Excel | |||
Using CreateEventProc fails when used with a Worksheet. | Excel Programming | |||
Excel crashes with createEventProc for a button | Excel Programming | |||
Excel crashes with createEventProc for a button | Excel Programming | |||
Excel crashes with createEventProc for a button | Excel Programming |