Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program Control After Closing Another Program
I have what I thought was a simple requirement. I have a workbook that has 2
simple choices to retrieve one workbook or another. When I click on the button the program is retrieved just fine., The userform works just fine and the save and close work just fine. The only issue is that when it "returns" to the original program, the menu that was there now disappears and has to be reloaded. Does anyone know of a way to return to the original program and still see the userform? Here is the code that opens the 2nd workbook. Private Sub GetTour_Btn_Click() Workbooks.Open ("Tour Quoting Program (Pricer).xls") Call DisplaySplash 'This line does not appear to be executing End Sub Sub DisplaySplash() Call HideApp Call RemoveCustomMenu Call AddCustomMenu On Error Resume Next Splash.Show On Error GoTo 0 End Su Any help would be appreciated. Thanks, Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program Control After Closing Another Program
Hi Steve,
Without know exactly whatt the called routines are doing I cannot be sure that this will answer your question but I suspect that VBA does not know which workbook etc it should be referring to. Declare some Public variables (at the top of a standard module in the Declarations area) and then set the variables to the desired workbooks. Then use the variables to refer to the desired workbook in your code. Declare the following Public variables in a standard module Public wbThis As Workbook Public wbOpened As Workbook Set wbThis = ThisWorkbook Set wbOpened = Workbooks.Open("Shelf Locations.xls") 'Examples of using the workbook variables wbOpened.Activate wbOpened.Sheets("Sheet2").Activate -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program Control After Closing Another Program
Thanks for your response, OssieMac. I tried your idea and it did not have
any effect on the results. The calling program still does not gain control after the called program is closed. Any other ideas? "OssieMac" wrote: Hi Steve, Without know exactly whatt the called routines are doing I cannot be sure that this will answer your question but I suspect that VBA does not know which workbook etc it should be referring to. Declare some Public variables (at the top of a standard module in the Declarations area) and then set the variables to the desired workbooks. Then use the variables to refer to the desired workbook in your code. Declare the following Public variables in a standard module Public wbThis As Workbook Public wbOpened As Workbook Set wbThis = ThisWorkbook Set wbOpened = Workbooks.Open("Shelf Locations.xls") 'Examples of using the workbook variables wbOpened.Activate wbOpened.Sheets("Sheet2").Activate -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program Control After Closing Another Program
Hello again Steve,
Is the Userform 'Splash' still visible when you attempt to show it in DisplaySplash? I am suspecting it is and that is the reason for the On Error Resume Next. You cannot show a UserForm modally when it is already visible. You need to hide it first. While hidden it is still loaded and you can make changes to it with other code. Try the following: Private Sub GetTour_Btn_Click() Workbooks.Open ("Tour Quoting Program (Pricer).xls") Call DisplaySplash 'This line does not appear to be executing End Sub Sub DisplaySplash() Splash.Hide 'Add this line (Could be prior to Call DisplaySplash) Call HideApp Call RemoveCustomMenu Call AddCustomMenu 'On Error Resume Next 'Should not be required Splash.Show 'On Error GoTo 0 End Sub -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program Control After Closing Another Program
Hey OssieMac,
I tried your code and still get the same results. It is frustrating that I can't get control back after saving and closing a program. Any other thoughts on this? Thanks! "OssieMac" wrote: Hello again Steve, Is the Userform 'Splash' still visible when you attempt to show it in DisplaySplash? I am suspecting it is and that is the reason for the On Error Resume Next. You cannot show a UserForm modally when it is already visible. You need to hide it first. While hidden it is still loaded and you can make changes to it with other code. Try the following: Private Sub GetTour_Btn_Click() Workbooks.Open ("Tour Quoting Program (Pricer).xls") Call DisplaySplash 'This line does not appear to be executing End Sub Sub DisplaySplash() Splash.Hide 'Add this line (Could be prior to Call DisplaySplash) Call HideApp Call RemoveCustomMenu Call AddCustomMenu 'On Error Resume Next 'Should not be required Splash.Show 'On Error GoTo 0 End Sub -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program Control After Closing Another Program
Hello again Steve,
I don't know how you are showing the Userform initially. When it is initially shown, is it being called from Sub DisplaySplash(). If so, have you got the Sub DisplaySplash() in the Userform module instead of a standard module. If in the Userform module then this causes problems. For example you could put the following code in any userform module and just attempting to run it before the Userform is displayed will show the userform but not run the code in the sub. Sub DisplayUserForm() MsgBox "Running this will display the userform but not display this MsgBox" End Sub Sub DisplaySplash() should be in a standard module. If this has not answered your question then I need to know what else the code is doing so perhaps you could share some code from the subs that are being called. Also you could try putting a MsgBox between all of the lines of code to see exactly what runs and what doesn't run like the following. Sub DisplaySplash() MsgBox "Sub DisplaySplash called" Call HideApp MsgBox "After HideApp" Call RemoveCustomMenu MsgBox "After RemoveCustomMenu" Call AddCustomMenu MsgBox "After AddCustomMenu" 'On Error Resume Next 'Comment out during testing Splash.Show MsgBox "After Splash.Show" 'On Error GoTo 0 End Sub -- Regards, OssieMac |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program Control After Closing Another Program
Hello OssieMac,
The DisplaySplash is in a standard module. I added the message boxes and all the messages display Except the "After Splash.Show". I have included the 3 sub routines that are called by the DisplaySplash subroutine. Sub HideApp() Application.Visible = False End Sub Public Sub RemoveCustomMenu() Dim cbWSMenuBar As CommandBar On Error Resume Next Set cbWSMenuBar = CommandBars("Worksheet Menu Bar") cbWSMenuBar.Controls("ExperienceIt Tours").Delete End Sub Public Sub AddCustomMenu() Dim cbWSMenuBar As CommandBar Dim muCustom As CommandBarControl Dim iHelpIndex As Integer Call RemoveCustomMenu Set cbWSMenuBar = Application.CommandBars("Worksheet Menu Bar") iHelpIndex = cbWSMenuBar.Controls("Help").Index Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, Befo=iHelpIndex, Temporary:=True) With muCustom .Caption = "&ExperienceIt Tours" With .Controls.Add(Type:=msoControlButton) .Caption = "&ExperienceIt Tours" .OnAction = "DisplaySplash" .FaceId = 9390 ' Face ID's for all of the buttons are in the Face ID file -- or run a macro to create the list End With End With End Sub That's it. I really appreciate your help. Thanks! "OssieMac" wrote: Hello again Steve, I don't know how you are showing the Userform initially. When it is initially shown, is it being called from Sub DisplaySplash(). If so, have you got the Sub DisplaySplash() in the Userform module instead of a standard module. If in the Userform module then this causes problems. For example you could put the following code in any userform module and just attempting to run it before the Userform is displayed will show the userform but not run the code in the sub. Sub DisplayUserForm() MsgBox "Running this will display the userform but not display this MsgBox" End Sub Sub DisplaySplash() should be in a standard module. If this has not answered your question then I need to know what else the code is doing so perhaps you could share some code from the subs that are being called. Also you could try putting a MsgBox between all of the lines of code to see exactly what runs and what doesn't run like the following. Sub DisplaySplash() MsgBox "Sub DisplaySplash called" Call HideApp MsgBox "After HideApp" Call RemoveCustomMenu MsgBox "After RemoveCustomMenu" Call AddCustomMenu MsgBox "After AddCustomMenu" 'On Error Resume Next 'Comment out during testing Splash.Show MsgBox "After Splash.Show" 'On Error GoTo 0 End Sub -- Regards, OssieMac |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Program Control After Closing Another Program
Hello again Steve,
I had the hide form in the wrong place. It needs to be before calling DisplaySplash. Put it in the button sub like following. Private Sub CommandButton2_Click() Workbooks.Open ("Tour Quoting Program (Pricer).xls") Splash.Hide 'Or can use Me.Hide Call DisplaySplash 'This line does not appear to be executing End Sub Alternatively open the forms properties and change ShowModal to False but bear in mind this allows the user to change worksheets while the form is open. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closing a program which was opened with shellexecute | Excel Programming | |||
Closing a program | Excel Programming | |||
Closing Excel Program correctly? | Excel Programming | |||
[HELP] closing a program with VBA code | Excel Programming | |||
Need program to ask me to save changes when closing | Excel Programming |