Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing VBA editor from Popping up
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing VBA editor from Popping up
maybe add this at the end
Application.VBE.MainWindow.Visible = false -- HTH Bob Phillips "jjk" wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing VBA editor from Popping up
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing VBA editor from Popping up
Yeah, it pops up and disappears doesn't it. I'll play around an d post back
if I come up with anything -- HTH Bob Phillips "jjk" wrote in message ups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing VBA editor from Popping up
Thanks for your effort Bob.
Did you manage to find anything? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing VBA editor from Popping up
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing VBA editor from Popping up
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does the research box keep popping up? | Excel Discussion (Misc queries) | |||
Popping out Message Box! | Excel Discussion (Misc queries) | |||
Clipboard Keeps Popping Up | Excel Discussion (Misc queries) | |||
Strange formula popping up | Excel Discussion (Misc queries) | |||
MsgBox keeps popping up | Excel Programming |