Userform Hide Workbook

[MajesticCamel]

Hello All,

I have searched online for this issue but couldn't find a proper solution.

I have a userform that open (while the workbook itself remains hidden) using the following code:

ThisWorkbook.Windows(1).Visible = False
    If Workbooks.Count = 1 Then
        Application.Visible = False
    End If
           
    UserForm1.Show

I first used only Application.Visible = False to hide the workbook but as that was hiding other workbooks, it created issues. Thus the code above.

Now the issue I am having is if the user form is open and the user opens a new Excel workbook, it will not show as Application.Visible = False was already done.

I tried also without the Application.Visible = False but the problem will be the Excel windows will show 'Empty' when using only the user form and no other workbook is open.

Is there any code or function that allows me to make the application visible once another workbook is opened.

Thanks.

 

[Jaafar Tribak]

Maybe something like this :

In the UserForm Module:

Option Explicit

Private WithEvents xl As Application

Private Sub UserForm_Initialize()
    Set xl = Application
    ThisWorkbook.Windows(1).Visible = False
    xl.Visible = False
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    xl.Visible = True
    ThisWorkbook.Windows(1).Visible = True
End Sub

Private Sub xl_NewWorkbook(ByVal Wb As Workbook)
    xl.Visible = True
End Sub

Private Sub xl_WorkbookOpen(ByVal Wb As Workbook)
    xl.Visible = True
End Sub

 

[MajesticCamel]

Thanks for the quick reply.

It is giving me an error or run time 91 when trying to close the user form.

also, the code solved my issue but it created the other issue i was avoiding which is creating an 'empty' window of excel. It might be cause of the same error , not sure.

 

[Dan_W]

You need to make sure you copy all of the code provided by Jaafar - the only way I can see that Error 91 could occur on closing the Userform is if it was missing the following line:

    Set xl = Application

As for the 'empty' window, I'm not sure I fully understand what you're after but if you want Excel to be invisible when there is only one workbook, but visible when there are two or more, then it should just be a simple change to the conditional code you helpfully provided above:

Option Explicit

Private WithEvents xl As Application

Private Sub UserForm_Initialize()
    Set xl = Application
    
    ThisWorkbook.Windows(1).Visible = False
    
    If Workbooks.Count = 1 Then
        xl.Visible = False
    Else
        xl.Visible = True
    End If
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    xl.Visible = True
    ThisWorkbook.Windows(1).Visible = True
End Sub

Private Sub xl_NewWorkbook(ByVal Wb As Workbook)
    xl.Visible = True
End Sub

Private Sub xl_WorkbookOpen(ByVal Wb As Workbook)
    xl.Visible = True
End Sub

If the error occurs again, please make a note on which line it breaks on.