keep excel not visible when other excel documents are opened

Question

I have an excel file with UserForm1. I have the following codes (see below) as soon as I open the file only UserForm1 is displayed and the file is not. So far everything fits but when I open another excel file, the invisible excel file (where UserForm1 is) becomes visible again. But I want it to remain invisible and only UserForm1 is visible, even if I open and close other excel files.

Code in my module:

Sub showLoginForm() If isSheetVisible Then ' Only Hide this workbook and keep the other workbooks visible ThisWorkbook.Windows(1).Visible = False Else ' There is no other workbook visible, hide Excel Application.Visible = False End If UserForm1.Show vbModeless End Sub Function isSheetVisible() As Boolean ' Checks if any workbook except the current one is visible Dim wb As Workbook For Each wb In Application.Workbooks If Not wb Is ThisWorkbook Then Dim win As Window For Each win In wb.Windows If win.Visible Then isSheetVisible = True Next End If Next End Function

Code in my workbook:

Private Sub Workbook_Open() UserForm1_Initialize 'ignore it not relevant for the question showLoginForm End Sub

Showing the problem visul (sorry cant get video)

Opening my test vba:

all good sofar now opening another excel file and my text excel will open as well, what i dont want:

I might need a code where when another file is opened after UserForm1 opened, UserForm1 is automatically asked if another file has been opened, if yes then ThisWorkBook.visible = False is used again. If so, how do I write such a code so that it will automatically be execute without doing anything?

when I open another excel file is it opened by users or VBA code? — taller
– taller, Commented Feb 15, 2024 at 23:01
So, I am opening the excel with vba than my UserForm1 opens and the excel workbook not sofar so good. But if i open now anothwr excel file. The hidden workbook (from userform1) opens which was hided. So thats my problem annd habe m365 as well :( — chn112
– chn112, Commented Feb 16, 2024 at 6:44
@taller I edited my question with pictures. I tried with new blank excel file and paste my codes but getting same issue. — chn112
– chn112, Commented Feb 16, 2024 at 9:45

taller · Accepted Answer · 2024-02-16 16:56:03Z

In VBE, right click on ThisWorkbook > View Code > paste the code in Code windows
The code hide the host workbook (which has UserForm) when users open a new workbook.

Note: The code is not guaranteed to resolve the OP's issue because I am unable to reproduce the problem.

Option Explicit Dim WithEvents xlApp As Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook) Me.Windows(1).Visible = False End Sub

I just added a code: ThisWorkbook.Windows(1).Visible = False to my showloginform on else and solve it. But, now i have second problem. If I close another excel doc. the UserForm1 also closes... do you know why?

Collectives™ on Stack Overflow

keep excel not visible when other excel documents are opened

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related