VB editor opens by magic

[dallag]

Hi there,
After the VB lines of code (ran in Excel)


Bla bla bla
'/*/*/*/*/*/*/*/*/*/*/*/*/
'Insert a proc into Sheet1
'/*/*/*/*/*/*/*/*/*/*/*/*/
Dim Startline As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
Startline = .CreateEventProc("Change", "Worksheet") + 2
..InsertLines Startline, "call Update_residuals"
End With
End If
retest = False
Workbooks(glbMainTrialFileName & ".xls").Save
Unload Me
Exit Sub


the VB editor opens by magic ?
could anybody tell me why ?

Dallag

 

[Chip Pearson]

That's just the way VBA is -- it opens the editor when you modify
a code module. Annoying, isn't it? You can write code to close
the window.

Application.VBE.MainWindow.Visible = False

You'll still get a flicker, but the window will close.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"dallag" <[email protected]>
wrote in message
news:[email protected]...

 

[Charlie]

Abra-Cadabra:

I got a compile error when I inserted your example code in the Workbook_Open
sub, closed and re-opened Excel. That caused the VB editor to automatically
open. The error was "End If without block If". Where is your "If" statement?

 

[dallag]

Thanks for your help fellas.
Abra-cadabra, sorry but the code was just a snap shot abd did not
include everything.
Thanks.

 

[Shailesh Shah]

Try this codes, which will not open VB Editor.

Sub test()
With ThisWorkbook.VBProject.VBComponents("sheet1").CodeModule
..InsertLines 1, "Private Sub Worksheet_Change(ByVal Target As Range)"
..InsertLines 2, "call Update_residuals"
..InsertLines 3, "End Sub"
End With
End Sub

Regards,
Shah Shailesh
http://in.geocities.com/shahshaileshs/
(Excel Add-ins Page)

*** Sent via Developersdex http://www.developersdex.com ***