Suppress VBA Window 1

[TimRHSD]

Hi,

I am creating a spreadsheet using vbScript. The script creates a WorkBook_BeforePrint event and works fine. However, I don't want the VBA window to open when the user runs the script. Any ideas on how to suppress the VBA Window from opening?

	Const vbext_ct_StdModule = 1
	Dim VBCodeMod 
	Dim LineNum 
	Dim VBComp 
	Dim StartLine
	Set VBComp = wb.VBProject.VBComponents.Add(vbext_ct_StdModule)

	'This is the print setup to be added to the Excel Workbook
	With wb.VBProject.VBComponents("ThisWorkbook").CodeModule
	    StartLine = .CreateEventProc("BeforePrint", "Workbook") + 1
	    .InsertLines StartLine, _
	    "Dim WS As Worksheet" & Chr(13) & _
		"	For Each WS In Worksheets" & Chr(13) & _ 
	    "	    With WS.PageSetup" & Chr(13) & _
	    "			.PrintTitleRows = ""$8:$9""" & Chr(13) & _
	    "			.LeftHeader = Range(""B3"")  & Chr(13) & Range(""B4"")" & Chr(13) &_
	    "			.LeftFooter = ThisWorkbook.FullName" & Chr(13) & _
	    "			.CenterFooter = ""Page &P of &N" & Chr(13) & _
        "			.RightFooter = ""&D" & Chr(13) & _
        "			.LeftMargin = Application.InchesToPoints(0.25)" & Chr(13) & _
        "			.RightMargin = Application.InchesToPoints(0.25)" & Chr(13) & _
        "			.Orientation = 2 " & Chr(13) & _
	    "   End With" & Chr(13) & _
	    "	Next"
	End With

Thanks for any help.

Tim

Tim Rutherford

http://www.Manna-Consulting.com

 

[TonyJollans]

Hi TimRNSD,

Try adding ..

[blue]Application.VBE.MainWindow.Visible = False[/blue]

.. at the end of your code.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[TimRHSD]

Hi Tony,

I tried your suggestion. However, it didn't work. When I read your answer, that looked exactly like what I was seeking.

Instead, I am formatting the spreadsheet from the report writer I am using.

Thanks,

Tim

Tim Rutherford

http://www.Manna-Consulting.com

 

[PHV]

it didn't work
Any error message ?
Have you tried this ?
wb.VBProject.VBE.MainWindow.Visible = False

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TimRHSD]

Thanks PVH,

That worked! Forgot, I had to apply it to the object with which I was using.

Tim

Tim Rutherford

http://www.Manna-Consulting.com

 

[TimRHSD]

Hi,

I can hide the VBE when I have a regular module (), but not when I try to add an event. The code works other than that. Any suggestions?

Thanks, Tim

Sub AddModule()
	Const vbext_ct_StdModule = 1	'Regular module
	Const vbext_ct_ClassModule = 2	'Class Module
	Const vbext_ct_Document = 100	'Event (Supposedly)
	Dim VBCodeMod, VBP 
	Dim LineNum 
	Dim VBComp 
	Dim StartLine
'	Hide VBE.  This works for a regular module, but not Events
	wb.VBProject.VBE.MainWindow.Visible = False
'	This works, but doesn't hide the VBE
	Set VBP = wb.VbProject   '.VBE.ActiveVBProject

	VBP.VBComponents.Add(vbext_ct_Document)

	Set vbComp = VBP.VBComponents("Sheet1").CodeModule
'	Add Event "WorkSheet_BeforeDoubleClick
	With vbComp 
	    StartLine = .CreateEventProc("BeforeDoubleClick", "WorkSheet") + 1
	    .InsertLines StartLine, _	
    	"	Application.DisplayAlerts = False" & Chr(13) & _
    	"	ActiveSheet.Unprotect password:=""elephant""" & Chr(13) & _ 
    	"	Selection.EntireRow.Insert Shift:=xlDown" & Chr(13) & _
    	"	ActiveSheet.Protect password:=""elephant"""
    End With
End Sub

Tim Rutherford

http://www.Manna-Consulting.com