Hello group,

I am writing code which adds a CheckBox and an associated Click event
handler to a worksheet. The event handler code is about 250 lines long.
I've tested the event handler code, and it works.

The problem is that Excel crashes while executing the .InsertLines
method. Visual Basic says "Automation error Exception occurred." and
then Excel crashes and attempts to recover files.

Worse, this happens sometimes, but not all the time, when the exact
same subroutine is called to create the CheckBox_Click code.

I have searched posts and read that some virus scanners react to
InsertLines statements. I have Norton Internet Security Professional. I
disabled Norton and I still get errors. Then I ran the code on a
different computer with no virus scanner and a different version of
Excel (2002 instead of 2003). It also crashes in the same way.

Any ideas what I should be doing differently to get around this?

Thanks!
Andy


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

This is more info on the same problem. I created a simple subroutine
which sticks a checkbox in every cell of the selected range, and
creates a simple event handler for the checkbox.

The code works great if the selection has only one cell. On the second
cell, it crashes, either in the OLEObjects.Add step or in the
..InsertLines step.

Any ideas why?

Here's the code:

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
Set ctl =
ActiveSheet.OLEObjects.Add(Classtype:="Forms.Check Box.1", _
Link:=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

Next cel

End Sub

Thanks!
Andy


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

In , asleeper
told us an interesting story. My reply to this story is at the bottom
of this message.

I have searched posts and read that some virus scanners react to
InsertLines statements. I have Norton Internet Security Professional.
I disabled Norton and I still get errors. Then I ran the code on a
different computer with no virus scanner and a different version of
Excel (2002 instead of 2003). It also crashes in the same way.

Just wanted to say this. The "InsertLines/virus scanners" problem is
not that Excel crashes, but that the AV "eats" the code module
containing InsertLines. I had this problem with McAfee ASAP.

--
Amedee Van Gasse

Hi Amedee,

Thanks for the information on McAfee. I think my client for this add-i
uses McAfee, so I will probably have to deal with that issue, after
can make my code work on my computer.

Or maybe I should find a way to avoid .InsertLines completely. Is ther
a way to react to ActiveX controls in a workbook using code that is no
in the VBProject associated with that workbook?

Thanks,
And

--
asleepe
-----------------------------------------------------------------------
asleeper's Profile: http://www.excelforum.com/member.php...fo&userid=2672
View this thread: http://www.excelforum.com/showthread.php?threadid=39990

In , asleeper
told us an interesting story. My reply to this story is at the bottom
of this message.


Hi Amedee,

Thanks for the information on McAfee. I think my client for this
add-in uses McAfee, so I will probably have to deal with that issue,
after I can make my code work on my computer.

Or maybe I should find a way to avoid .InsertLines completely. Is
there a way to react to ActiveX controls in a workbook using code
that is not in the VBProject associated with that workbook?

Thanks,
Andy

Can't help you there, sorry.
My use of InsertLines was for a different type of problem, so my
workaround wouldn't be much use for you.

--
Amedee Van Gasse

I find the same problem. Sometimes crashing (usually), sometimes not. The
first checkbox add works fine. The next one crashes on the Add.

My code is similar to yours.

Sub AddCancelCheckbox(rowNum%)
Dim cb As OLEObject
Dim cellA As Range

Set cellA = Cells(rowNum%, cancelCol%)

Set cb = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Check box.1")
With cb
.Top = cellA.Top
.Left = cellA.Left
With .Object
.Caption = " "
.AutoSize = True
.FontSize = 8
.Alignment = fmAlignmentLeft
End With
End With

With ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).CodeModule
lineNum! = .CreateEventProc("Change", cb.Name)
.InsertLines lineNum! + 1, "Call CancelProduct(" & rowNum% & ", " &
cb.Name & ".Value)"
End With
End Sub

--
Amber


"asleeper" wrote:


This is more info on the same problem. I created a simple subroutine
which sticks a checkbox in every cell of the selected range, and
creates a simple event handler for the checkbox.

The code works great if the selection has only one cell. On the second
cell, it crashes, either in the OLEObjects.Add step or in the
.InsertLines step.

Any ideas why?

Here's the code:

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
Set ctl =
ActiveSheet.OLEObjects.Add(Classtype:="Forms.Check Box.1", _
Link:=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

Next cel

End Sub

Thanks!
Andy


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