Hide VBProject while copying Forms & Modules to another workbook

[MikeZz]

Hi,
I have an automated application that modifies a number of workbooks by
adding some forms and modules to each one, then saves and closes them.

During this process, I use Application.visible = false to hide all the
opening/closing workbooks so that the user can do other work without focus
jumping to Excel.

The problem is that for some reason, the Visual Basic window opens at some
point and never closes. This defeats the purpose of making the application
visible and I don't know why it's opening the window. It also confuses the
user running the application because she doesn't know anything about excel.

Any ideas on what command is actually making VBA visible?
Any ideas on how to make it invisible?

This one's a real bear because if I put a "Stop" or Pause in VBA, VBA
automatically becomes visible so there's no way for me to see what's tripping
the visiblity property.

Thanks,
MikeZz


Here is the basic routine that copies over the forms and modules:
Public Sub SendForms()
errMaster = "SendForms: Sending Macros"

srcWB.VBProject.VBComponents("frmNotFound").Export Filename:=sStr
destWb.VBProject.VBComponents.Import Filename:=sStr
srcWB.VBProject.VBComponents("StartupForm").Export Filename:=sStr
destWb.VBProject.VBComponents.Import Filename:=sStr
srcWB.VBProject.VBComponents("ExportCode").Export Filename:=sStr
destWb.VBProject.VBComponents.Import Filename:=sStr
Kill sStr

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Const DQUOTE = """" ' one " character

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("ThisWorkbook")
Set CodeMod = VBComp.CodeModule

With CodeMod
LineNum = .CreateEventProc("Open", "Workbook")
LineNum = LineNum + 1
.InsertLines LineNum, " ThisWorkbook.Sheets(" & DQUOTE &
"VeryHidden" & DQUOTE & ").Visible = xlVeryHidden"
LineNum = LineNum + 1
.InsertLines LineNum, " StartupForm.Show"
End With
End Sub

 

[Peter T]

I don't think there is any way to prevent the VBE from opening when you do
CreateEventProc

There are various ways to close it when done, following is probably the
simplest

Dim cbc As CommandBarControl
Set cbc = Application.VBE.CommandBars.FindControl(ID:=752)
cbc.Execute

Regards,
Peter T

 

[MikeZz]

Hi Peter,
This seems to do what it's supposed to.
Is there a way to check if VBA is open at the beginning of my code and then
only close it if it wasn't open at the start?

Thanks again for the support,
MikeZz

 

[Peter T]

It's a bit odd but when VBA code is runnning all the VBE windows "exist",
even if the VBE is not apparently open. Following should confirm if the "a"
VBE is user opened but bear in mind it could "belong" to some other app that
hosts VBA, eg Word or even another instance of Excel, typically the chances
of that are fairly small. With rather more work it would be possible verify
if "your" project is in the VBE.


Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA"
( _
ByVal hwnd As Long, ByVal lpString As String, _
ByVal cch As Long) As Long

Private Declare Function IsWindowVisible Lib "user32.dll" ( _
ByVal hwnd As Long) As Long

Function VBEexists() As Boolean
Dim hwnd As Long
Dim sBuff As String * 128
Const cCLSNAME As String = "wndclass_desked_gsk"
Const cWINTEXT As String = "Microsoft Visual Basic"

hwnd = FindWindow(cCLSNAME, vbNullString)
If hwnd Then
If IsWindowVisible(hwnd) Then
Call GetWindowText(hwnd, sBuff, 128)
VBEexists = Left$(sBuff, Len(cWINTEXT)) = cWINTEXT
End If
End If

End Function

Regards,
Peter T