0 votes
in Education by (1.7m points)
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.

JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

1 Answer

0 votes
by (1.7m points)
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

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
...