运行代码后，Excel菜单栏冻结.右键单击后解冻

Question

我正在使用带有按钮的表单从Intranet下载表，并用源表中的数据填充此目的地表。此部分有效：数据填充，形成卸载，脚本停止，我将目的地表作为活动屏幕。
现在出现的问题是：我可以选择单元格，但不能在Excel中使用任何功能，无法在快速访问功能带上推出任何按钮，例如文件，主页或保存。推ESC无济于事。Excel的唯一一件事是将鼠标按钮按下随机单元格的某个地方。它可以"解锁" Excel，我可以使用文件菜单。我不知道在哪里看。我认为这是很小的东西，但无法弄清楚这是什么。

这是代码（跳跃用于单个或多项项目。如果是1个项目，我会跳过循环。也许有一个更好的选择？）：

Private Sub genbutton_Click()
    On Error GoTo 0
    Dim startsheet As String
    startsheet = ActiveSheet.Name
    roww = ActiveCell.Row
    Set Source = ActiveWorkbook.ActiveSheet
    'search for column in source sheet
    kolommaint = kolomnaam2("Maintenance Plan")
    kolomfloc = kolomnaam2("Functional Location")
    kolomdescrip = kolomnaam2("Maintenance item description")
    kolomequip = kolomnaam2("Equipment")
    'find last row on data source page
    With ActiveSheet
        lastrow = .Cells(.Rows.Count, kolommaint).End(xlUp).Row
    End With
    Set destinationsheet = Workbooks.Open("http:// sheet on intranet.xlsm")
    'find first data row on destination sheet
    Dim FindString As String
    Dim Rng As Range
    FindString = "Action"
    With destinationsheet.Sheets("Data input").Range("A:A")
        Set Rng = .Find(What:=FindString, _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
        If Not Rng Is Nothing Then
            Application.Goto Rng, True
            datarij = ActiveCell.Row + 1
        End If
    End With
    'make a jump to avoid loop if only 1 item is needed
    If callsingle.Value = True Then
        i = roww
        If Source.Range(kolommaint & i).Value = "" Then
            GoTo verdergaan
        End If
        GoTo jump
    End If
    For i = eerstedatarij To lastrow
jump:
        'skip row if empty
        If Source.Rows(i).Hidden = True Then
            GoTo verdergaan
        End If
        destinationsheet.Sheets("Data input").Range("A" & datarij).Value = "Release Call"
        destinationsheet.Sheets("Data input").Range("B" & datarij).Value = Source.Range(kolommaint & i).Value
        destinationsheet.Sheets("Data input").Range("C" & datarij).Value = "PM"
        destinationsheet.Sheets("Data input").Range("E" & datarij).Value = Source.Range(kolomdescrip & i).Value
        destinationsheet.Sheets("Data input").Range("F" & datarij).Value = Source.Range(kolomdescrip & i).Value
        destinationsheet.Sheets("Data input").Range("G" & datarij).Value = Source.Range(kolomfloc & i).Value
        destinationsheet.Sheets("Data input").Range("H" & datarij).Value = Source.Range(kolomequip & i).Value
        datarij = datarij + 1
        'make jump if single item is used
        If callsingle.Value = True Then
            GoTo jump2
        End If
verdergaan:
    Next i
jump2:
    destinationsheet.Sheets("Data input").Range("A13").Select
    Set Source = Nothing
    Set destinationsheet= Nothing
    Unload Me
End Sub

我目前正在使用下面的代码来解冻Excel：

Private Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
 Const MOUSEEVENTF_RIGHTDOWN = &H8
 Const MOUSEEVENTF_RIGHTUP = &H10
 Public Sub RightDown()
     mouse_event MOUSEEVENTF_RIGHTDOWN, 0, 0, 0, 0
     mouse_event MOUSEEVENTF_RIGHTUP, 0, 0, 0, 0
 End Sub

Answer 1

我在Excel功能上冻结有类似的问题。我相信，用" worksheets.open"打开工作表时的焦点错误引起的

执行" Worksheets.open"时，在用户形式可见时出现问题。我必须先隐藏用户形式，然后打开工作表以避免冻结问题。

导致excel函数上冻结的代码：

Private Sub btnBrowse_Click()
  Dim retVal As Integer
  Dim fDialog As FileDialog
  Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
  fDialog.AllowMultiSelect = False
  retVal = fDialog.Show()
  If retVal = -1 Then
    Application.Workbooks.Open fDialog.SelectedItems(1)
    Me.Hide   ' Hiding my userform
  End If
End Sub

，但我找到了用隐藏的冻结的解决方案，并显示了工作表窗口。

  Dim aWorkbook As Workbook
  Set aWorkbook = ActiveWorkbook
  aWorkbook.Windows(1).Visible = False
  aWorkbook.Windows(1).Visible = True

然后起作用。唯一的缺点是更改可见度使工作表处于变化状态。如果可能的话，最好只是在打开工作簿之前隐藏用户形式。