Cannot close workbook after running sub with userform

Question

I have a macro that opens a userform to capture a start and end date. After clicking OK on the userform, a file dialog box opens to select an Excel Workbook to open.

Immediately after I run the below sub, I can't close the workbook that is opened by using the 'X' in the top-right corner. I also can't save the workbook by clicking the save icon.

However, if I click on another workbook or switch to a different sheet in the workbook that was opened, and then click back to the one opened by the sub everything works as it's supposed to.

Also, I replace the userform with two input boxes, to capture each of the two dates, I am able to close the workbook that is opened with no issue.

Maybe there's something funny with the userform code?

This is all that is in the userform.

 Private Sub Ok_button_Click()
 call module1.forecast
 unload userform1
 end Sub

And this is the main sub.

 Sub forecast()
 dim start_SFY as long
 dim end_SFY as long
 dim filesToOpen as object
 dim wb as workbook

 Application.ScreenUpdating= False

 start_SFY = userform1.textbox1.value
 end_SFY = userform1.textbox2.value

 set filesToOpen = application.fileDialog(msoFileDialogOpen)
 filesToOpen.show
 set wb = application.workbooks.open(filesToOpen.selecteditems(1),false)

 Application.ScreenUpdating= True
 End Sub

Here's the sub showing userform1

 Sub run_userform()
 userform1.show
 End Sub

Also, here is the Excel version:

Excel 2013 64-bit (15.04753.1003) Part of Microsoft Office 365 ProPlus

Can someone maybe try to replicate the issue that I'm having? I'm wondering if this is an issue related to my employer's version of Excel or something?

This sort of thing has never happened to me before.

Also, I can close the program with VBA. It's just when trying to click the 'X' that it won't close.

Update:

I was able to get the code, with no changes, to work fine at home on Excel 2016. I'm going to get a coworker to test on their system today.

When I was home, I didn't put a button to call the sub on a worksheet. I called it from the VBA editor. After some testing this morning, it seems that the button is the issue. If I call the sub from the VBA editor, I can close the opened workbook. However, if I use a command button (form control, not ActiveX as I get an error saying, "Cannot draw object" whenever I try to add any kind of ActiveX object to a worksheet) the opened workbook will not close.

I think I have found the problem

This issue seems to be with the 'form control command button'. ActiveX was disabled in the Trust Center. When I enabled it and created a command button, I was able to close the opened workbook. I then tried the command form button again, and could not close the opened workbook. I was also successfully able to close the opened workbook when I ran the sub from the sub listbox in the developer tab, and when I place the sub in the Excel Ribbon and ran it from there.

Any idea as to why the control form command button would cause this issue?

Answer 1

Try fileOpenerForm Show 0 to open open it with Modal = False (Makes the macro run while the Userform is visible)

Do not forget to add fileOpenerForm.Hide later on.

Also Load fileOpenerForm and Unload fileOpenerForm may be useful

Answer 2

The root of the problem is the change to using multiple excel interfaces in 2013. Microsoft addresses the issue in the Solutions for SDI Issues section of this page.

A workbook cannot be closed by clicking the red "X" Close button when that workbook is programmatically opened via a modal user form. To work around this issue, it is suggested that you add the following code to the user form Layout event procedure and then open the user form as modeless

Private Sub UserForm_Layout()
    Static fSetModal As Boolean
    If fSetModal = False Then
        fSetModal = True
        Me.Hide
        Me.Show 1
    End If
End Sub

Another option is to open the workbook window, activate any other window, and then reactivate the workbook window. You should now be able to close the workbook using the Close button.

So these are the two options they present and one I came up with.

Option 1) You can switch your dialog to modeless using their code, or by setting the ShowModel property to false in your userform.

Option 2) As you discovered, manually switching between workbooks after opening via modal userform resyncs everything. Not a good solution for the users of my code, and I don't recommend relying on it.

Option 3) It's worth mentioning, if you don't open the file via the userform then there's no issue. So if last thing the userform needs to do is open the file, you can easily save the file path in a string, unload the troublesome userform and move the workbooks.open call after closing. Here's an example of what I mean

Public EDIT_FILE_DIRECTORY As String

Public Sub Main()

    fileOpenerForm.Show 

    If EDIT_FILE_DIRECTORY <> "" Then
        Call Workbooks.Open(EDIT_FILE_DIRECTORY)
    End If

End Sub

And in the userform something along these lines, where the filename is created based on userform parameters and a listbox selection:

Private Sub OpenSelectedWorkbooks_Button_Click()

    Dim workbookName As String

    workbookName = selectionList.Item(Me.FileSelection_ListBox.ListIndex + 1)

    EDIT_FILE_DIRECTORY = ROOT_DIR & GetSelectedSubfolder & "\" & workbookName

    Unload Me

End Sub