Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
avoiding VBEopen when using CreateEventProc
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
avoiding VBEopen when using CreateEventProc
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
avoiding VBEopen when using CreateEventProc
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
avoiding VBEopen when using CreateEventProc
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
avoiding VBEopen when using CreateEventProc
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CreateEventProc for OLE button crashes Excel | Excel Programming | |||
Using CreateEventProc fails when used with a Worksheet. | Excel Programming | |||
Excel crashes with createEventProc for a button | Excel Programming | |||
Excel crashes with createEventProc for a button | Excel Programming | |||
Excel crashes with createEventProc for a button | Excel Programming |