I am writing code that adds a CheckBox to a worksheet with a Click event
handler in the worksheet code module.

The problem is that the InsertLines method crashes Excel with a "Automation
error Exception occurred" message.

This happens whether my virus scanner is enabled or disabled. It also
happens on a different machine with no virus scanner. It happens identically
in Excel 2002 and 2003. But oddly, it does not happen every time I use
..InsertLines. Sometimes it works fine.

Any ideas why Excel might be crashing?

Thanks,
Andy

Andy
Have you disabled events, calc etc?
also make sure you have a few doevents in your code to let vba catch
up.
Adding vba from vba is always a bit flaky - its modifyiing itself
whilst running.

I have found its more stable to save the code as a text file and then
import it. Also you need to avoid running code in the module you are
modifying if at all possible.

You should also probably clean the code before and after - check out
Rob Boveys code cleaner - www.appspro.com or write your own. And I
have also ended up saving, closing and re-opening some files to help
stability.

Of course it also depends what else you are doing.

cheers
Simon


--
Simon Murphy
------------------------------------------------------------------------
Simon Murphy's Profile: http://www.excelforum.com/member.php...o&userid=26538
View this thread: http://www.excelforum.com/showthread...hreadid=399907

Hi Simon,

Thank you for your really good ideas! I wish I didn't have to write VBA
with VBA, but my client wants to create and delete CheckBox controls in
a template which I must create from scratch, VBA and all. I'm supposed
to be making my client's life easier, not mine, therefore I must
suffer. ;)

But I am not running anything in the module I am writing to, at least
not while I'm writing it.

Your time and events suggestions sounded very promising. I tried them,
but so far to no avail.

Here's my simple code that crashes every time, but only if you select
two or more cells before running it. It works great on a single-cell
selection.

Sub AddCheckBoxes()
' For each cell in the selection, sticks a check box, with a simple
event handler

Dim cel As Range
Dim ctl As OLEObject

For Each cel In Selection
Application.EnableEvents = False
Set ctl =
ActiveSheet.OLEObjects.Add(Classtype:="Forms.Check Box.1", _
Link:=False, Top:=cel.Top + 2, Left:=cel.Left + 2,
Height:=10, Width:=10)
MsgBox "box in ok?"
Application.EnableEvents = True
DoEvents
Application.EnableEvents = False
With
ActiveWorkbook.VBProject.VBComponents(ActiveSheet. CodeName).CodeModule
..InsertLines .CountOfLines + 1, "Private Sub " & ctl.Name
_
& "_Click()" & Chr(13) _
& " MsgBox """ & ctl.Name & """" & Chr(13) _
& "End Sub"
End With
MsgBox "code in ok?"
Application.EnableEvents = True
DoEvents
Next cel

End Sub

Any ideas would be most welcome!

Thanks very much for your time,
Andy


--
asleeper
------------------------------------------------------------------------
asleeper's Profile: http://www.excelforum.com/member.php...o&userid=26723
View this thread: http://www.excelforum.com/showthread...hreadid=399907

Andy
Thanks for the code - that explains things.
There seems to be a prob writing from within a loop.
I suggest you build the string of all routines then write just once
outside a loop.
no idea whats causing the prob
heres some code (seems to work on my pc):


Sub AddCheckBoxes()
' For each cell in the selection, sticks a check box, with a simple
event handler

Dim cel As Range
Dim ctl As OLEObject
Dim strVBA As String

Application.EnableEvents = False

For Each cel In Selection
Set ctl =
ActiveSheet.OLEObjects.Add(Classtype:="Forms.Check Box.1", _
Link:=False, Top:=cel.Top + 2, Left:=cel.Left + 2, Height:=10,
Width:=10)
MsgBox "box in ok?"
DoEvents
strVBA = strVBA & vbCrLf & "Private Sub " & ctl.Name _
& "_Click()" & Chr(13) _
& " MsgBox """ & ctl.Name & """" & Chr(13) _
& "End Sub"
Next cel

With
ActiveWorkbook.VBProject.VBComponents(ActiveSheet. CodeName).CodeModule
InsertLines .CountOfLines + 1, strVBA
End With
MsgBox "code in ok?"
Application.EnableEvents = True

End Sub

cheers
Simon


--
Simon Murphy
------------------------------------------------------------------------
Simon Murphy's Profile: http://www.excelforum.com/member.php...o&userid=26538
View this thread: http://www.excelforum.com/showthread...hreadid=399907