Creating an event procedure question

[bhofsetz]

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:

Please Login or Register  to view this content.

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.

[Norman Jones]

Hi Bhofsetz,

Try inserting:

Application.VBE.MainWindow.Visible = False

before End Sub.


---
Regards,
Norman



"bhofsetz" <[email protected]> wrote in
message news:[email protected]...
>
> 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("ThisWorkbook").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
>

[bhofsetz]

Thank you Norman, just what I needed it to do.