Folks

Is this also crashing your Excel ?! If so anything obvious I might have missed ?

----

Sub doStuff()

Dim x As OLEObject
Dim aName As String

For Each x In ActiveSheet.OLEObjects

aName = x.Name

With ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).CodeModule
.InsertLines .CreateEventProc("Click", aName) + 1, _
"Msgbox ""Hi there"" "
End With

Next x

End Sub

Is this also crashing your Excel

Oh yeah! And when I sent the crash report to MS I got a web page that said
MS is aware of the issue (first time I ever saw that).

--
Jim Rech
Excel MVP
"Alex T" wrote in message
om...
| Folks
|
| Is this also crashing your Excel ?! If so anything obvious I might have
missed ?
|
| ----
|
| Sub doStuff()
|
| Dim x As OLEObject
| Dim aName As String
|
| For Each x In ActiveSheet.OLEObjects
|
| aName = x.Name
|
| With
ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).CodeModule
| .InsertLines .CreateEventProc("Click", aName) + 1, _
| "Msgbox ""Hi there"" "
| End With
|
| Next x
|
| End Sub

Oh yeah! And when I sent the crash report to MS I got
a web page that said MS is aware of the issue (first time I ever saw that)

Well well... thanks for the feedback...

Any known workaround ?

Regards

Alex

Hi Alex,

Oh yeah! And when I sent the crash report to MS I got
a web page that said MS is aware of the issue (first time I ever saw that)

Well well... thanks for the feedback...

Any known workaround ?

Generally when automating the IDE, it's not a good idea to modify your own
project. What are you trying to achieve?

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

Generally when automating the IDE, it's not a good idea to modify
your own project. What are you trying to achieve?

I'm trying to programmatically insert a click event handler for each
control in my worksheet (I have hundreds of them and I haven't found a
solution a have centralized event handler, see
http://tinyurl.com/2y3kd ).

So you'd say that this would work having the code running from a
different workbook ?

Regards

Alex

Hi Alex,

I'm trying to programmatically insert a click event handler for each
control in my worksheet (I have hundreds of them and I haven't found a
solution a have centralized event handler, see
http://tinyurl.com/2y3kd ).

Well, I have quite a few issues with code that programmatically
modifyies itself at runtime:

1. To add code like this, the user has to have "Trust Access to Visual
Basic Project" enabled and your project can't be protected.

2. Adding code to a project usually results in that project needing to
be recompiled, causing loss of global variables.

3. It can crash the IDE (as you found).

So you'd say that this would work having the code running from a
different workbook ?

I'd put it differently: Copy the worksheet to a new workbook and add
the controls and code to it there, then throw it away when it's
finished.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

I've been trying for a few days to add code to a workbook created
"on-the-fly" The workbook also has controls added "on-the-fly" so I need to
add the code to support the controls.

Have had a lot of difficulty but I believe I've come up w/ a solution:
I've added a commented line of dashes into the CodeModule in one sub and
then added the remaining code into the CodeModule in a separate sub.

I think the compiler generates a compile request when the code is all added
at once and the code preceeds the Option Explicit line in the declaration
section causing my Excel to crash.

This same code is added to each of 4 worksheet codemodules


Here's the example:

Sub 1

With objProject.VBComponents(MyWkSht.CodeName).CodeModu le
.InsertLines Line:=1, String:="'-----------------------------------------"
End With

Sub 2

With objProject.VBComponents(MyWkSht.CodeName).CodeModu le

..InsertLines Line:=.CountOfLines + 1, String:="Dim blnAnswer2 as Boolean" &
vbCr & _
"Dim strPrompt2 as String" & vbCr & _
"'-------------------------"
.InsertLines Line:=.CountOfLines + 1, String:="Sub chkHideAll_Click" & vbCr
& _
" strPrompt2 = ""This is My Test""" & vbCr & _
" blnAnswer2 = MsgBox(strPrompt2, vbokonly)" & vbCr & _

"End Sub"

The CountOfLines definitely gets the code after the Option Explicit
statement and it has worked a number of times in a row - no crashes.

Good luck, let me know if you have a problem, I've not tested this
extensively.

"Alex T" wrote:

Folks

Is this also crashing your Excel ?! If so anything obvious I might have missed ?

----

Sub doStuff()

Dim x As OLEObject
Dim aName As String

For Each x In ActiveSheet.OLEObjects

aName = x.Name

With ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).CodeModule
.InsertLines .CreateEventProc("Click", aName) + 1, _
"Msgbox ""Hi there"" "
End With

Next x

End Sub