Program Control After Closing Another Program

[Steve]

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

 

[OssieMac]

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

 

[Steve]

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]

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

 

[Steve]

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]

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

 

[Steve]

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,
Before:=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]

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.

 

[Steve]

Hi OssieMac,

I implemented your suggestions and I still cannot get control to the calling
program after closing the called workbook. I'm going to take a different
approach, so please do not spend any more time trying to fix this. You have
done enough already.

THANK YOU!