J
Jag Man
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
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