I am using the example on Chip Pearson's web page
http://www.cpearson.com/excel/vbe.htm for creating an even procedure in
another workbook for formatting printed output from that workbook.
I have modified his code as follows:


Code:
--------------------
Sub CopytoThisWorkbook()
Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("BeforePrint", "Workbook") + 1
.InsertLines StartLine, _
" dim NumRows as integer" & vbCr & _
" NumRows = Activesheet.usedrange.rows.count" & vbCr & _
" With Activesheet.pagesetup" & vbCr & _
" .PrintTitleRows = ""$2:$3""" & vbCr & _
" .PrintArea = ""$A$4:$K$"" & NumRows" & vbCr & _
" .PrintHeadings = False" & vbCr & _
" .PrintGridlines = False" & vbCr & _
" .PrintComments = xlPrintNoComments" & vbCr & _
" End With"
End With
End Sub
--------------------


It works as expected and creates the Workbook_BeforePrint procedure.
However whenever the code runs it opens a VBA window and shows the
newly created code. No errors, just gives focus to the VBA window.
This isn't a problem for me while I'm programming/debugging, but the
end user shouldn't see this.

Any ideas why this is happening and how to keep it from happening?

Thanks for any help/suggestions.


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=377417

Hi Bhofsetz,

Try inserting:

Application.VBE.MainWindow.Visible = False

before End Sub.


---
Regards,
Norman



"bhofsetz" wrote in
message ...

I am using the example on Chip Pearson's web page
http://www.cpearson.com/excel/vbe.htm for creating an even procedure in
another workbook for formatting printed output from that workbook.
I have modified his code as follows:


Code:
--------------------
Sub CopytoThisWorkbook()
Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("BeforePrint", "Workbook") + 1
.InsertLines StartLine, _
" dim NumRows as integer" & vbCr & _
" NumRows = Activesheet.usedrange.rows.count" & vbCr & _
" With Activesheet.pagesetup" & vbCr & _
" .PrintTitleRows = ""$2:$3""" & vbCr & _
" .PrintArea = ""$A$4:$K$"" & NumRows" & vbCr & _
" .PrintHeadings = False" & vbCr & _
" .PrintGridlines = False" & vbCr & _
" .PrintComments = xlPrintNoComments" & vbCr & _
" End With"
End With
End Sub
--------------------


It works as expected and creates the Workbook_BeforePrint procedure.
However whenever the code runs it opens a VBA window and shows the
newly created code. No errors, just gives focus to the VBA window.
This isn't a problem for me while I'm programming/debugging, but the
end user shouldn't see this.

Any ideas why this is happening and how to keep it from happening?

Thanks for any help/suggestions.


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile:
http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=377417

Thank you Norman, just what I needed it to do

--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880
View this thread: http://www.excelforum.com/showthread.php?threadid=37741