LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 38
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 Crashes Whenever Add-In Button is Clicked Richard S Setting up and Configuration of Excel 0 August 28th 07 12:50 AM
Using CreateEventProc fails when used with a Worksheet. George Doster Excel Programming 3 October 22nd 03 04:22 PM
Excel crashes with createEventProc for a button Dave Peterson[_3_] Excel Programming 0 August 25th 03 11:31 PM
Excel crashes with createEventProc for a button Tom Ogilvy Excel Programming 4 August 25th 03 08:30 PM
Excel crashes with createEventProc for a button tina salgia Excel Programming 0 August 25th 03 06:17 PM


All times are GMT +1. The time now is 05:26 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"