VB editor opens by magic

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

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" <dallag.20c0ry_1135071001.1359@excelforum-nospam.com>
wrote in message
news:dallag.20c0ry_1135071001.1359@excelforum-nospam.com...
>
> 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
>
>
> --
> dallag
> ------------------------------------------------------------------------
> dallag's Profile:
> http://www.excelforum.com/member.php...o&userid=29762
> View this thread:
> http://www.excelforum.com/showthread...hreadid=494813
>

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" wrote:

>
> 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
>
>
> --
> dallag
> ------------------------------------------------------------------------
> dallag's Profile: http://www.excelforum.com/member.php...o&userid=29762
> View this thread: http://www.excelforum.com/showthread...hreadid=494813
>
>

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

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 ***