Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide VBProject while copying Forms & Modules to another workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide VBProject while copying Forms & Modules to another workbook
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide VBProject while copying Forms & Modules to another workbo
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" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide VBProject while copying Forms & Modules to another workbo
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 "MikeZz" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete all modules and forms from workbook | Excel Programming | |||
Copying UserForms & Modules to another workbook | Excel Programming | |||
Public, Private, Event modules, Forms modules,,, | Excel Programming | |||
Extracting (copying) modules from one workbook to another. | Excel Programming | |||
Copying VBA modules from one workbook to another with a macro? | Excel Programming |