2

I have set up a new, empty, modeless userform, to fix my problem with the least amount of code involved.

For when the workbook is opened, the following code is executed to hide Excel and show the userform. It's the only code for the workbook.

Private Sub Workbook_Open()
    UserForm1.Show
    If Application.Windows.Count <> 1 Then
        Application.Windows("test.xlsm").Visible = False
    Else
        Application.Visible = False
    End If
End Sub

I have an empty userform with one single button. The only code for this userform is:

Private Sub CommandButton1_Click()
    Application.Windows("test.xlsm").Visible = True
    Application.Visible = True
Unload Me
End Sub

The last thing is a button on the first worksheet, to start the same process as when the workbook is opened. Its code:

Sub Button1_Click()
    UserForm1.Show
    If Application.Windows.Count <> 1 Then
        Application.Windows("test.xlsm").Visible = False
    Else
        Application.Visible = False
    End If
End Sub

Now my problem: When I open the workbook, the userform shows up, but excel and the active window stay visible as well. However, if I click the button on the worksheet, Excel, or the window, are hidden as they should. Also, Excel, not the userform, has focus after loading everything.

The first time I ran this, it worked fine. I suspect changing the ShowModal setting in the editor screwed it up somehow, but that's just me guessing. Anyway, it doesn't work anymore as intended, no matter the modal setting now.

If I just run

Application.Visible = False

instead of the "if"-clause, Excel still stays visible and of course so does the active window.

This is driving me nuts. What am I missing?

Edit: Link to my test file: Test File on my Dropbox Might have to start it twice, because when the macros are blocked at startup and only activated after excel has completely loaded, the code works as intended.

Edit: I was able to test this on an excel 2010 pc and there the problem doesn't exist. So it might have something to do with the way newer Office Apps handle stuff.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Baconsandwich
  • 23
  • 1
  • 5
  • Do you have any other workbooks, including hidden ones? According to Help, `Application.Windows` includes hidden windows. – SJR Mar 22 '19 at 15:20
  • Nope, no other windows. I created this test file from a new clean workbook. Nothing else is open. – Baconsandwich Mar 22 '19 at 16:40
  • You definitely don't have a personal workbook? – SJR Mar 22 '19 at 19:00
  • 2
    I checked …\AppData\Roaming\Microsoft\Excel\XLSTART to be sure. It's as empty as my bank account. – Baconsandwich Mar 22 '19 at 20:32
  • Ha, I would have suggested you hire somebody but perhaps not. Try stripping everything out and adding it back a bit at a time and then you should be able to see when it breaks. So just start with the Workbook Open code, remove the If and check that works, etc – SJR Mar 22 '19 at 20:49
  • Deleted the userform and started again, step by step. The problem appears, when I turn the ShowModal setting for the form to false. If it's True, Excel hides and the userform appears on top. If I set it to false, Excel stays open (or maybe immediately reappears) and the userform loses focus and even appears behind other windows, if any are open. Setting `vbModal = True` wouldn't work for the project I need this for, though, because there I need multiple userforms open and editable at the same time, and `vbModal = True` would prevent that. – Baconsandwich Mar 22 '19 at 21:15
  • Interestingly enough though, if I run the exact same code on a button on the worksheet instead of under workbook_open, Excel hides, no matter the modal setting. – Baconsandwich Mar 22 '19 at 21:18

4 Answers4

1

I found myself having the exact same problem - modeless form opened with Workbook_Open() event that's also supposed to hide excel app (working on Excel 2016, 32bit).

The reason why it's working with UserForm property ShowModal set to True is because: the execution is suspended - it's waiting for user to interact with the UserForm that was shown.

If we change ShowModal to False (or call UserForm.Show vbModeless) then the execution is never suspended and once we reach End Sub of our Workbook_Open(), Excel appears to set Application.Visible = True on its own.

Only solution I've found thus far is this one - basically you suspend the execution by adding an infinite loop so Excel only gets to end of this event once you get rid of (Unload/Hide) the form that was shown previously.

My version looks like this:

Private Sub Workbook_Open()
    Dim App As Object
    Set App = startMenu

    App.Show vbModeless

    While App.Visible
        DoEvents
    Wend
End Sub

Then just to make sure that Excel is closed once that modeless UserForm is closed I've added this:

Private Sub UserForm_Terminate()
    CloseApp
End Sub

Public Sub CloseApp()
    ThisWorkbook.Saved = True

    If Not OtherWorkbooksOpen Then
        Application.Quit
    Else
        ThisWorkbook.Close
    End If
End Sub

Public Function OtherWorkbooksOpen()
    If Application.Workbooks.Count > 1 Then
        OtherWorkbooksOpen = True
    Else
        OtherWorkbooksOpen = False
    End If
End Function

EDIT:

Solution without infinite loop - schedule hiding of Excel:

Private Sub Workbook_Open()
    Dim App As Object
    Set App = startMenu

    App.Show
    If Not OtherWorkbooksOpen Then
        Application.OnTime Now + TimeValue("00:00:01"), "HideExcel"
    End If
End Sub
Hajta
  • 66
  • 5
  • Thank you a lot for this! It wasn't quite the solution, but I found it through the links you provided. I just had to put my `If Application.Windows.Count <> 1 Then Application.Windows("test.xlsm").Visible = False Else Application.Visible = False End If` under `UserForm_Activate()` not in the workbook code! It now works like I wanted it to. Thanks again! Maybe this would fix it for you too. Making a loop superfluous. – Baconsandwich Apr 02 '19 at 18:27
0

I think the userform1.show needs to be called after the execution of if statement.

 Private Sub Workbook_Open()
    If Application.Windows.Count <> 1 Then
        Application.Windows("test.xlsm").Visible = False
    Else
        Application.Visible = False
    End If
    UserForm1.Show
End Sub
Arjun
  • 133
  • 1
  • 10
  • Thanks for the answer. I changed the order but it didn't fix it. :( – Baconsandwich Mar 22 '19 at 16:39
  • Try running the above code with showmodal property set to 'True'. I guess in your userform the showmodal is set to False. [More info on Show Modal property](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/showmodal-property) – Arjun Mar 22 '19 at 17:02
  • If i set the userform to modal it works, but that's not an option for me, because the project I need this to work for, requires multiple userforms to be open and editable at the same time. If I set any one userform there on modal, the others won't be accessible by the user. – Baconsandwich Mar 22 '19 at 17:17
0

Not an answer, but I can't post this as a comment. This works for me - the user form appears, the application is hidden. I used "<>2" as I have a personal workbook. Can you confirm what happens for you?

 Private Sub Workbook_Open()
    If Application.Windows.Count <> 2 Then
        Application.Windows("test.xlsm").Visible = False
    Else
        Application.Visible = False
    End If
    UserForm1.Show False
End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26
  • Sadly, this did nothing for me. However, thanks to this I noticed that `Application.Windows(filename).visible = false` works in `Workbook_Open` but `Application.Visible = False` doesn't, or maybe gets immediately set back to `True` – Baconsandwich Mar 23 '19 at 13:17
0

I had the same issue, but I notice the form loads before the excel file. So I put a redundancy calling the application.visible = false, in the form. Basically after clicking anything in the form, it will call the application.visible = false and the excel window will hide.

Max Arias
  • 1