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").Co deModule
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

VB editor opens by magic
 

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"
wrote in message
...

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").Co deModule
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

VB editor opens by magic
 

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").Co deModule
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

VB editor opens by magic
 

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


--
dallag
------------------------------------------------------------------------
dallag's Profile: http://www.excelforum.com/member.php...o&userid=29762
View this thread: http://www.excelforum.com/showthread...hreadid=494813

VB editor opens by magic
 

Try this codes, which will not open VB Editor.

Sub test()
With ThisWorkbook.VBProject.VBComponents("sheet1").Code Module
..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 ***