Excel 365访问其他工作簿后如何将焦点切回指定工作表

该现象并非Excel 365版本bug，原因是选择第二个数据范围时如果选中了其他工作簿的区域，Excel会将该工作簿置为前台活动窗口，仅调用wbOne.Activate只会激活工作簿，不会自动切换到新建的工作表标签，批量运行时Excel为了性能会跳过部分非必要的屏幕刷新，就会出现焦点停留在原工作簿的情况，逐步骤运行时每步都会触发屏幕刷新，因此功能正常。

修改代码逻辑，补充工作表激活和强制屏幕刷新语句即可：

1. 在ListDif子过程开头添加关闭自动屏幕刷新的语句，减少运行过程中不必要的窗口切换闪烁
2. 在无错误分支下，激活工作簿后额外激活新建的工作表
3. 无论运行是否报错，最终都恢复屏幕刷新设置为默认开启状态

修改后的完整代码 #

Const PROC_NAME = "LIST DIFFERENCES"
Const ERR_USR_CANCEL = 111

Dim wbOne As Workbook, ws1 As Worksheet, rg As Range

Sub ListDif()
  Dim shDataMix As Worksheet
  
  ' 运行前关闭屏幕刷新，减少闪烁
  Application.ScreenUpdating = False
  
  On Error GoTo EH:
  
  DataRead
 
  Set shDataMix = wbOne.Sheets.Add(before:=ws1)
  
  With shDataMix
    .Cells(1, 1) = "Something"
  End With
  
EH:
  If Err.Number <> 0 Then
    Select Case Err.Number
      Case ERR_USR_CANCEL
        MsgBox "Canceled by user", vbExclamation, PROC_NAME
      Case Else
        MsgBox Err.Description, vbExclamation, PROC_NAME
    End Select
  Else
    wbOne.Activate
    ' 激活新建的工作表
    shDataMix.Activate
  End If
  
  ' 无论是否报错，最终都恢复屏幕刷新
  Application.ScreenUpdating = True
  
  Set rg = Nothing
  Set wbOne = Nothing
  Set ws1 = Nothing
  Set shDataMix = Nothing
End Sub

Private Sub DataRead()
  Set wbOne = ActiveWorkbook
  Set ws1 = ActiveSheet
  With ws1
    On Error Resume Next
    Set rg = Application.InputBox(prompt:="Select the FIRST data range", _
      Title:=PROC_NAME, Type:=8)
    On Error GoTo 0
    If rg Is Nothing Then
      Err.Raise ERR_USR_CANCEL
    Else
      With rg
        Debug.Print .Rows.Count
      End With
    End If
  End With
    
  Set rg = Nothing
  On Error Resume Next
    Set rg = Application.InputBox("Select the SECOND data range", _
        PROC_NAME, Type:=8)
  On Error GoTo 0
  If rg Is Nothing Then
    Err.Raise ERR_USR_CANCEL
  Else
    With rg
      Debug.Print .Rows.Count
    End With
  End If
End Sub

内容的提问来源于stack exchange，提问作者Patone