Userform closed when closing another open workbook

[BJems]

Hello,

i have tried so many things but not answer.

Can somebody help me.
Everythings are on title.

How can i attach xlsm file here ?

Thanks for Your help
Best regards

 

[jasonb75]

You can't attach a file, you can post code by clicking the </> icon above then pasting your code into the popup wndow.

As a last resort, you can post a link to a file on a file share site like dropbox, but it is preferred that you only do so as a last resort if we are unable to answer your question from the information that you provide in your thread.

 

[BJems]

Ok thanks.

So, in ThisWorkbook i written this :

Private Sub Workbook_Open()
Application.EnableEvents = False
    'Application.IgnoreRemoteRequests = True 
    Application.Visible = False
    RaccourciQ23.Show
Application.EnableEvents = True
End Sub

In the UserForm :

Private Sub UserForm_Initialize()
    Me.Caption = ThisWorkbook.Name
    Me.StartUpPosition = 0
    Me.Left = Application.Left + (0.5 * Application.Width) - (0.5 * Me.Width)
    Me.Top = Application.Top + (0.5 * Application.Height) - (0.5 * Me.Height)
End Sub

Private Sub Quitter_Click()
'Unload RaccourciQ23
Application.Quit
End Sub

and tested with this on the Module, but didn't help :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wb As Workbook

For Each wb In Workbooks
    If swb <> wb.Name Then
        wb.Close
        'Application.Visible = False
        RaccourciQ23.Show vbModeless
    Else: ThisWorkbook.Close
    End If 
Next wb
End Sub

thanks

 

[jasonb75]

Workbook_BeforeClose (or any similar event procedure) only works with the workbook holding the code, not any workbook.

Without setting up a test file, I suspect that you would need to return focus to the userform. It could be that it is hidden, not closed.
The problem you will have is that with the activity being on another workbook, there doesn't appear to be anything to use as an event trigger.

 

[BJems]

Here the link of the file :

Raccourcis.xlsm

drive.google.com

 

[jasonb75]

Ok, you might need to talk me through what you're doing and what problems you're seeing.

I've opened your file, then opened and closed several other workbooks, the form always stays open.

 

[BJems]

Thanks for your quick response.

You need to change Application.Visible = False
After you open the file, the UserForm is opening.
After you open another excel file and close this file.
It will close the UserForm too and not juste the another excel file

 

[jasonb75]

I did find similar when I was testing but thought that I was looking at the wrong thing.

Because you have a userform open, Application.Visible = False only hides the workbook, not the whole application. You must have the application visible for the userform to be visible.

As a result of this, you can't close the last visible file by clicking the X in the top right corner of the window as that is trying to close the entire excel application.

Closing the last workbook by going to File > Close doesn't appear to cause any problems. Similarly closing the last visible workbook by using a close command in vba appears to work fine.

I was able to block the use of the X in the top corner when there is only 1 visible workbook by using the following code in the userform module,

Private Sub Quitter_Click()
'Application.IgnoreRemoteRequests = False
'Unload RaccourciQ23
KeepOpen = False

End Sub

This in the 'ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = KeepOpen
End Sub

and adding the line

KeepOpen = True

into the Workbook_Open code.

The downside is that this means that you would need to use one of the alternative methods to close the last visible workbook.
If the above fixed the problem for you then it should be as simple as adding a quick for each workbook loop into the close event and killing the other workbook from there.

 

[BJems]

Hello,

in ThisWorkbook :

Private Sub Workbook_Open()

With Application
.Visible = False 'if = False the application is not more visible
.WindowState = xlMaximized
.Windows(ThisWorkbook.Name).Visible = True
End With
RaccourciQ23.Show
KeepOpen = True

End Sub

in UserForm :

Private Sub Quitter_Click()

KeepOpen = False
Unload RaccourciQ23
Application.Quit

End Sub

?

I tried it, but when i close the other Workbok thie File/Close it close the file and open another File.
Another solution to do it, maybe witout UserForm but directly in a Sheet ?

 

[jasonb75]

I don't understand why you're trying to hide the application, then make the workbook visible.

What you're trying to do is logically impossible and appears to be the root cause of the problems.

If closing one workbook is opening another then I would imagine that there is code in the file being closed to do that. There is no other reason for it to happen.

Also, use Application.Quit with caution. That will close everything, not just the userform.

Using a sheet as the form might be more stable for what you're trying to do, at least that would remove the need to try and hide the application in the background.