the code is very sensitive to how it is used.
your first step should be to use my code as is and insert your Excel VBA.
--
http://www.standards.com/; See Howard Kaikow's web site.
"jjk" wrote in message
oups.com...
Hi Howard,
That is a very interesting example. I learnt a lot from it.
However, I am still facing the same problem.
I guess the instant that
'ActiveWorkbook.VBProject.VBComponents(ActiveSheet .Name).CodeModule' is
modified the VBE pops up.
I have an existing workbook and I am trying to add code to it. I am not
trying to create a new one.
I have attached a more complete code for your reference
Dim cel As Range
Dim obj As OLEObject
Set cel = ActiveCell
Set obj = ActiveSheet.OLEObjects.Add(classtype:="Forms.Combo Box.1",
Top:=cel.Top, Left:=cel.Left, Width:=cel.MergeArea.Width + 3,
Height:=cel.MergeArea.Height + 3)
With ActiveWorkbook.VBProject
With .VBComponents(ActiveSheet.Name).CodeModule
codeStart = .CreateEventProc("Change", obj.Name)
.InsertLines codeStart + 1, "msgbox " & obj.Name &
".Object.value" & vbCrLf & "msgbox """ & obj.Name & """"
End With
.VBE.MainWindow.Visible = False
End With
Thanks,
Jayant