Preventing VBA editor from Popping up

[jjk]

Hi,

I am programmatically adding event handlers to controls on a spread
sheet. These controls are also added dynamically.
Please find below the piece of code that I am using.

With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
codeStart = .CreateEventProc("Change", obj.Name)
.InsertLines codeStart + 1, _
"msgbox " & obj.Name & ".Object.value" & vbCrLf & _
"msgbox """ & obj.Name & """"
End With

This bit works fine, but there is an unnecessary side effect.
The VBA Editor pops up displaying the module that was modified.

At the present, I am closing the editor by sending the Alt-F4 key.
However, this is very klunky as the popping up of the editor is very
obvious in slow systems.

Is there any way to prevent it? Can workbook protection help?

Any suggestions would be most welcome.

Thanks,
Jayant

 

[Bob Phillips]

maybe add this at the end

Application.VBE.MainWindow.Visible = false

 

[jjk]

Thanks Bob.

That is definitely a better solution than send the Alt-F4 key.
But it still pops up the VBE window after the code addition.
Is there any way of preventing the window from popping up in the first
place?

Thanks,
Jayant

 

[Bob Phillips]

Yeah, it pops up and disappears doesn't it. I'll play around an d post back
if I come up with anything

 

[jjk]

Thanks for your effort Bob.
Did you manage to find anything?

 

[Howard Kaikow]

I've just gone thru several weeks of dealing with this and related issues in
creating http://www.standards.com/index.html?SetVBAProjectPassword.

If you take the skeleton of that code and insert the CodeModule creation,
the VBE should not op up,

 

[jjk]

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.ComboBox.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

 

[Howard Kaikow]

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.