Excel 2016 Bug?

[Gaddy]

I am experiencing a replicable problem with Excel 2016.


I've created an application, that will work flawlessly in Excel 2010, but not in Excel 2016. In the app, the user selects a workbook from which they will be asked to select multiple, seperate ranges.


There's a form with multiple buttons, each of which asks them to select a range related to a paricular item from the previously selected workbook.


When the button is clicked, the selected workbook should be active so they can select the range. This is the case in Excel 2010, but not in Excel 2016.


In Excel 2016, the workbook that the userform exists in keeps displaying instead of the selected workbook.


To replicate the problem, the below steps can be followed:

• Open Excel 2016
• Create 2x workbooks
• In Workbook 1, create a userform
• Create a command button with the below code behind it.
• Activate Workbook1
• Initialise the form and click the button
• Select any range (Workbook2 should be active)
• Click the button again...

The problem: Workbook2 is no longer active when selecting a second range. (Excel 2010 will keep Workbook2 active - everytime).


Other than selecting the Switch Windows function, the only way I've manged to select a second range from Workbook2, is to click over the Cancel button and release the mouse elsewhere, i.e., don't actually click Cancel.
This will display Workbook2, however, you cannot select a range yet; a range can only be selected after clicking into the dialog box (from what I've discovered).


Also, I've just noticed that if you initialise the userform with Workbook2 being active, when you click the button, Workbook1 will activate instead.
<code>Private Sub CommandButton1_Click()

Dim rng As Range

Me.Hide

Workbooks("Book2").Activate

On Error Resume Next

Set rng = Application.InputBox("Select range", "Select Range", Type:=8)

On Error GoTo 0

Me.Show
End Sub</code> Could this be down to Microsoft moving from MDI to SDI for Excel 2013 and above?
More importantly, how do I get around this issue?

 

[rlv01]

I suppose we can add this to the list of little behaviors that have changed for 2016. To work around, activate the 2nd workbook before showing the userform:

Sub ShowForm()
    Workbooks("Book2").Activate
    UserForm1.Show
End Sub

or if you want to go back to the first workbook when you exit the form:

Sub ShowForm()
    Workbooks("Book2").Activate
    UserForm1.Show
    Workbooks("Book1").Activate
End Sub

 

[Gaddy]

Hi,

Thanks for the response. I did test your way prior to creating the thread (it worked) but it would involve rewriting a chunk of my code which I'd rather avoid, though it looks like I may have to just for Excel 2016.

 

[rlv01]

That's Microsoft for you, always adding new "features". Another workaround would be to add a global worksheet variable at the top of the userform code module, then use it in the form's "activate" method:

Option Explicit

Dim DesignatedWorkbook As Workbook

Private Sub UserForm_Activate()
    If Not DesignatedWorkbook Is Nothing Then
         DesignatedWorkbook.Activate
    End If
End Sub

Private Sub CommandButton1_Click()

Dim rng As Range

Me.Hide
Workbooks("Book2").Activate

Set DesignatedWorkbook = ActiveWorkbook  '<-- add this line
 
On Error Resume Next
Set rng = Application.InputBox("Select range", "Select Range", Type:=8)
On Error GoTo 0

Me.Show
End Sub