Thanks to the contributions of this forum I have been able to create a
function which inserts into the CodeModule behind Sheet2 Selection
Change code (as below).

The problem I have is that when the function is called VBE is fired up
and displays the CodeModule it's just written into.

How can I avoid this?

Matthew

Function insertSelectionChange(wkbNew As Workbook)
'Dim wkbNew As Workbook
'Set wkbNew = Workbooks.Add
Dim startPoint As Long

With wkbNew.VBProject.VBComponents("sheet2").CodeModule
startPoint = .CreateEventProc("selectionChange", "worksheet") + 1
..InsertLines startPoint, "'Application.ScreenUpdating = true" &
Chr(13) & _
" If selection.Row = 1 Or _" & Chr(13) & _
" selection.Interior.ColorIndex = 3 Or _" & Chr(13) & _
" selection.Areas.Count 1 Then Exit Sub" & Chr(13) & _
" If CBool(InStr(1, selection.EntireColumn.End(xlUp), ""Type"")) =
True Then" & Chr(13) & _
" If selection.Rows.Count = 1 And selection.Count = 15 Then" &
Chr(13) & _
" If assayFileModule.carryOverQuery2(selection.Rows.Cou nt) =
True Then" & Chr(13) & _
" Call assayFileModule.storeCmpds_sheet(selection)" &
Chr(13) & _
" Else" & Chr(13) & _
" Exit Sub" & Chr(13) & _
" End If" & Chr(13) & _
" ElseIf selection.Rows.Count = 7 And selection.Count = 105 And
CBool(InStr(1, selection.Cells(1).offset(, -2).Value, ""plate"")) =
True Then" & Chr(13) & _
" If assayFileModule.carryOverQuery2(selection.Rows.Cou nt) =
True Then" & Chr(13) & _
" Call assayFileModule.storeCmpds_sheet(selection)" &
Chr(13) & _
" Else" & Chr(13) & _
" Exit Sub" & Chr(13) & _
" End If" & Chr(13) & _
" End If" & Chr(13) & _
" End If"
End With
End Function

Add this code

Application.VBE.MainWindow.Visible = False

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
Thanks to the contributions of this forum I have been able to create a
function which inserts into the CodeModule behind Sheet2 Selection
Change code (as below).

The problem I have is that when the function is called VBE is fired up
and displays the CodeModule it's just written into.

How can I avoid this?

Matthew

Function insertSelectionChange(wkbNew As Workbook)
'Dim wkbNew As Workbook
'Set wkbNew = Workbooks.Add
Dim startPoint As Long

With wkbNew.VBProject.VBComponents("sheet2").CodeModule
startPoint = .CreateEventProc("selectionChange", "worksheet") + 1
.InsertLines startPoint, "'Application.ScreenUpdating = true" &
Chr(13) & _
" If selection.Row = 1 Or _" & Chr(13) & _
" selection.Interior.ColorIndex = 3 Or _" & Chr(13) & _
" selection.Areas.Count 1 Then Exit Sub" & Chr(13) & _
" If CBool(InStr(1, selection.EntireColumn.End(xlUp), ""Type"")) =
True Then" & Chr(13) & _
" If selection.Rows.Count = 1 And selection.Count = 15 Then" &
Chr(13) & _
" If assayFileModule.carryOverQuery2(selection.Rows.Cou nt) =
True Then" & Chr(13) & _
" Call assayFileModule.storeCmpds_sheet(selection)" &
Chr(13) & _
" Else" & Chr(13) & _
" Exit Sub" & Chr(13) & _
" End If" & Chr(13) & _
" ElseIf selection.Rows.Count = 7 And selection.Count = 105 And
CBool(InStr(1, selection.Cells(1).offset(, -2).Value, ""plate"")) =
True Then" & Chr(13) & _
" If assayFileModule.carryOverQuery2(selection.Rows.Cou nt) =
True Then" & Chr(13) & _
" Call assayFileModule.storeCmpds_sheet(selection)" &
Chr(13) & _
" Else" & Chr(13) & _
" Exit Sub" & Chr(13) & _
" End If" & Chr(13) & _
" End If" & Chr(13) & _
" End If"
End With
End Function

OK, I can see that should work.
Thanks.

I can't help feeling it would be better to prevent VBE from firing up
rather than allowing it to and then hiding the fact.

I suppose it's inevitable that VBE fires up, given that I'm modifying
code?

Matthew,

I think so, I have never found a way of stopping it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
ups.com...
OK, I can see that should work.
Thanks.

I can't help feeling it would be better to prevent VBE from firing up
rather than allowing it to and then hiding the fact.

I suppose it's inevitable that VBE fires up, given that I'm modifying
code?

Bob,

I tried yr suggestion, which seemed like a good one, but find that my
call to my function which adds the eventproc apparently turns visible
back to true.

code snippet is
Application.VBE.MainWindow.visible = False
Debug.Print "Application.VBE.MainWindow.visible(1)?: " &
Application.VBE.MainWindow.visible
Call commonFunctions.insertSelectionChange(wkbNew)
Debug.Print "Application.VBE.MainWindow.visible(2)?: " &
Application.VBE.MainWindow.visible

resulting immediate window is
Application.VBE.MainWindow.visible(1): False
Application.VBE.MainWindow.visible(2): True

Is this typical behavior or does it reflect some behavior of the code
I'm writing to the SheetModule? (no, can't be the latter, the only
thing it does there and then is Dim startPoint As Long so I can get the
..CreateEventProc off to a good start)

Obviously enough suggestions gratefully received at this point; the
running App looks pretty dreadful with the VBE popping up in the
middle; will definately scare off the average punter! )-;

Matthew