I'm using a an xlsm file form that calls up a second form to ask the user to select which workbook to open. The VBA code will then open that workfile. However, I'm finding that in Office 2016, the file opens behind the original xlsm file. That is, it will not become the active window. I can use Alt+Tab to go to the open file, but the file will not interact with my keystrokes or mouse clicks until I Alt+Tab to activate the originating xlsm file. Then when I reactive the VBA-opened file, I can interact with it. That is, the file that VBA opened will not become active until I touch base back with the workfile that has the VBA code. I've tried a series of activeworkbooks, application.activewindow commands to activate the opened workbook to no avail.
Here is the code that I'm using:
Private Sub btn_select_Click()
' User clicks on select. If a subcategory is selected open the file and stop program. If not repopulate sub-category
If IsNull(lst_subcategories) Or lst_subcategories = "" Then
lst_subcategories.Clear
Call populate_data
Else ' open file and unload form (End)
If lst_datacategories <> "Tourism" Then
str_filetoopen = "I:\Bureau-Work\Data System\" & lst_datacategories & "\" & lst_subcategories & ".xlsx" ' May need to be F: drive
Else
str_filetoopen = "I:\Bureau-Work\Data System\" & lst_subcategories & "\" & lst_subcategories & ".xlsx" ' May need to be F: drive
End If
On Error Resume Next ' drive or file may not be available. If so error and retain from
Set wb = Workbooks.Open(str_filetoopen)
If wb Is Nothing Then
MsgBox "I: Drive or Retrieve File is Not Accessible"
GoTo handle ' retains frm_editData
End If
Workbooks(wb).Activate
ActiveWindow.Visible = True
End 'exit all macros and forms once successfully loading file
End If
handle:
End Sub
The "Workbooks(wb).Activate" and "Activewindow.visible=True" commands are attempts to set the new workbook window as the active window. To the best of my knowledge, this does not happen in Office 2013, but may be peculiar to Office 2016.
