llowwelll wrote ...

However, when I went to place this code into my
full application, I get the 'Microsoft Excel has encountered an error
and needs to close' message.

Yeah, I've experienced these issues:

http://groups.google.com/groups?hl=e...TNGP10.phx.gbl

The problem seems to occur when the workbook containing the
dynamically created controls is saved. My experience was such a
workbook could not be subsequently saved without going GPF. To ensure
the controls aren't saved in the workbook, you must trap the
_BeforeSave event, teardown the controls, save the workbook and, if
the workbook is not closing, recreate the controls. Of course you must
also persist all the info to enable you to create the controls in the
_Open event.

Chip Pearson's code. Have you noticed this... in the code below,
run Test1 immediately followed by Test2 and explain why the m_strTest
variable is empty when Test2 is executed:

Option Explicit

Private m_strTest As String

Sub Test1()
m_strTest = "Chip"
ChipsCode
MsgBox m_strTest
End Sub

Sub test2()
MsgBox m_strTest
End Sub

Sub ChipsCode()
Dim OLEObj As OLEObject
Dim Rng As Range
Dim WS As Worksheet
Dim CodeMod As Object
Dim LineNum As Long
Set WS = ActiveSheet
Set Rng = Range("G10")

Set OLEObj = _
WS.OLEObjects.Add(classtype:="Forms.CommandButton. 1", _
Top:=Rng.Top, Left:=Rng.Left, Height:=Rng.Height * 2, _
Width:=Rng.Width * 2)
OLEObj.Name = "MyButton"
OLEObj.Object.Caption = "Click Me"
Set CodeMod = _
ThisWorkbook.VBProject.VBComponents(Sheet1.CodeNam e).CodeModule
LineNum = CodeMod.CreateEventProc("Click", OLEObj.Name)
CodeMod.InsertLines LineNum + 1, _
"Msgbox ""You clicked me"" "
End Sub

I came to the conclusion that programmatically adding controls to a
worksheet at run-time is too high risk for production code. In my most
recent Excel project I reluctantly took the decision to have a
permanent number of controls and dynamically hide the ones I didn't
need. Philosophically unsatisfactory but the correct decision for a
very important customer.

--