User form closed when other excel workbook closed

I have a vba user from in Excel 2013.  The workbook the form is with is hidden when the user opens the file and the form is displayed.  The form is used to generate a separate workbook.  Problem is when I close that other workbook it closes my user form and tries to save the workbook behind the user form rather than the workbook that was generated.  Any ideas?  THanks!
LVL 6
HyperBPPAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Are you using Before Close Event to close the form? If yes, please share the code.
HyperBPPAuthor Commented:
Yes.

Private Sub Workbook_BeforeClose(cancel As Boolean)
    ThisWorkbook.Saved = True
End Sub
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
No. This code will not close the userform, if you close the other workbook.
I was asking that what code you are using to hide the userform?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

HyperBPPAuthor Commented:
Not hiding the userform.  Hiding the workbook on opening:

Private Sub Workbook_Open()
    Application.Visible = False
    EditModule.Show vbModeless
End Sub
HyperBPPAuthor Commented:
The userforms closes when I close another separate workbook not connected to the userform other than the userform created that workbook.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Remove all your codes from ThisWorkbook Module from your workbook which has this UserForm and place the following codes on ThisWorkbook Module to see if your issue gets resolved.
Public swb As String
Private Sub Workbook_Open()
   Application.ScreenUpdating = False
   swb = ThisWorkbook.Name
   ThisWorkbook.Application.Visible = False
   EditModule.Show vbModeless
   Application.ScreenUpdating = True
End Sub
Private Sub Workbook_BeforeClose(cancel As Boolean)
   If swb <> ActiveWorkbook.Name Then
      ActiveWorkbook.Close
      Application.Visible = False
      EditModule.Show vbModeless
   Else
      ThisWorkbook.Close
   End If
End Sub

Open in new window

HyperBPPAuthor Commented:
when it closes it still attempts to close my running workbook with the form that's hidden.  I can cancel the saving but I don't want the user to have to see that either.  It's close.  But something is still awry.

Thanks!
Roy CoxGroup Finance ManagerCommented:
Try displaying the user form modeless and hiding it after the new workbook is created. See the attached example
TestForm.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HyperBPPAuthor Commented:
@sktneer that almost works.  But it still closes the calling form when I close any workbook for some reason even though it only specifies to close the active workbook.

@Roy_cox  didn't seem to make a difference.
Roy CoxGroup Finance ManagerCommented:
If you are hiding the Excel application are you sure that the userform WorkBook is still not running in the background? Check with Task Manager to see.
HyperBPPAuthor Commented:
It occurs whether or not I hide the workbook.  Even if it is not hidden it tries to close.
HyperBPPAuthor Commented:
If I select save or don't save the process is not running when I check the task manager.
Roy CoxGroup Finance ManagerCommented:
Can you attach the workbook
HyperBPPAuthor Commented:
Press the export checklist in the GUI.  Then exit that checklist.  It should not try to close the module.
C--Users-ttalbott-Desktop-Option-C-.xlsm
Roy CoxGroup Finance ManagerCommented:
When I follow your instructions a new workbook is generated. I then close the new workbook but the master workbook remains open with the userform displayed. I've done this several times changing the Stae but the master workbook with the userform stays open.
HyperBPPAuthor Commented:
Hmmmm.  I wonder why it happens to me and not you?  I'm in 2013.  Any ideas?  Thanks!
HyperBPPAuthor Commented:
When I close the checklist it immediately gives me the Microsoft excel prompt to close "CustomModule_Cleaned.xlsm".... Also I'm on windows 7.  Maybe this is an office bug?  So weird and frustrating.
Roy CoxGroup Finance ManagerCommented:
Sorry for the dealy but I've not been around for a couple od days.

I don't know why it works for me, I'll test again later on a different computer.
HyperBPPAuthor Commented:
Thanks!
Roy CoxGroup Finance ManagerCommented:
I'll try to test it at work tomorrow
HyperBPPAuthor Commented:
What if I open a separate execution of Excel and use that to generate the workbook?  I've tried that but I can't seem to copy to that workbook if it's in a separate instance.  Any thoughts?
HyperBPPAuthor Commented:
I should note in this that if I trace the closing call in workbook before close it passes on closing the first workbook but the BeforeClose is not called when it tries to close the main workbook.  It just skips this completely for some reason.

Workbook_BeforeClose(cancel As Boolean)
 
   If swb <> ActiveWorkbook.Name Then
      Application.DisplayAlerts = False
      ActiveWorkbook.Close
      'Application.Visible = False
      EditModule.Show vbModeless
      Application.DisplayAlerts = True
   Else
     Application.DisplayAlerts = False
      ThisWorkbook.Close
    Application.DisplayAlerts = True
   End If
End Sub
Roy CoxGroup Finance ManagerCommented:
This code can only work in the workbook that contains it. Similar code can be put into the other workbook

Maybe

Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wb As Workbook
On Error GoTo err_exit

Application.DisplayAlerts = False

For Each wb In Workbooks

    If swb <> wb.Name Then
        wb.Close
        'Application.Visible = False
        EditModule.Show vbModeless
    Else: ThisWorkbook.Close
    End If
    
err_exit:
    Application.DisplayAlerts = True
End If
End Sub

Open in new window

HyperBPPAuthor Commented:
The other workbook is generated by the current workbook.  Should I set this code programmatically?
HyperBPPAuthor Commented:
Ok, I figured something out that seems to work.  However, it leaves an Excel instance hanging in the background.  Is there a way to make sure that doesn't happen?  Cuz then it causes and error on reopening.
Roy CoxGroup Finance ManagerCommented:
Did you try the code that I posted above in the main workbook?
HyperBPPAuthor Commented:
That didn't work for me.  Had the same issue.  Here is what I did (basically added cancel = true) if I'm closing the generated workbook.  However, after I've closed the main workbook I still have a excel instance hanging in the background which causes issues when I try to open the workbook again.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
   If swb <> ActiveWorkbook.Name Then
      Application.DisplayAlerts = False
      ActiveWorkbook.Close
      Application.Visible = False
      EditModule.Show vbModeless
     
      Application.DisplayAlerts = True
      Cancel = True
   Else
     Application.DisplayAlerts = False
      ThisWorkbook.Close
    Application.DisplayAlerts = True
   End If
End Sub
Roy CoxGroup Finance ManagerCommented:
Your code must be hiding the Excel application somewhere. I'm sure that I mentioned this in a previous post.
HyperBPPAuthor Commented:
@Roy_Cox.. Her are the opening lines of the code.  

Private Sub Workbook_Open()
   Application.ScreenUpdating = False
   swb = ThisWorkbook.Name
   ThisWorkbook.Application.Visible = False
   EditModule.Show vbModeless
   Application.ScreenUpdating = True
End Sub

So I do hide the "This Workbook" but I then close this workbook.  When I close the workbook how do I ensure the application closes as well?
Roy CoxGroup Finance ManagerCommented:
Your example open code does not hide the application. I would really recommend not hiding it.

However you can use the userform terminate event to make sure the application is made visible.

Private Sub UserForm_Terminate()
ThisWorkbook.Application.Visible = True
End Sub

Open in new window

HyperBPPAuthor Commented:
The customer wants it hidden :(
Martin LissOlder than dirtCommented:
I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an "answer".
Roy CoxGroup Finance ManagerCommented:
I have actually provided how to ensure the application is restored to visibility when the userform is closed. At that stage it is simple to to close the application or continue.

It is worrying that the OP is obviously creating this for a "customer" but has no idea how to code for this!
HyperBPPAuthor Commented:
I'll have to ask the other question in a separate post on why the hanging excel.  Giving you credit for displaying it even though I suppose it leaves a remaining error.  That can be addressed in another thread.  And by the way Roy_Cox, we all don't get to perfectly align our skills with what the customer wants so we have to develop solutions.  I guess that's troubling for you because you come from a perfect world you popped out of the womb with pure VBA skills.  ;)
Roy CoxGroup Finance ManagerCommented:
No I learned the hard way and I would not attempt anything that I could not achieve. particularly for a paying customer. If you want to be sure that the application actually closes then you need error handlers within the code that will apply Application.Quit should an error occur that may leave an invisible instance of Excel.

In a case like this I would find out why the client felt it necessary to hide Excel and present different options that are more easily manageable.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.