Userform Hide Workbook

MajesticCamel

New Member
Joined
Jan 19, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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:

VBA 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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
9,015
Office Version
  1. 2016
Platform
  1. Windows
Maybe something like this :

In the UserForm Module:
VBA Code:
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

New Member
Joined
Jan 19, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 11, 2018
Messages
1,507
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
    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:

VBA Code:
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.
 

Forum statistics

Threads
1,182,140
Messages
5,933,866
Members
436,915
Latest member
Cygne volant

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top