Excel VBA按钮 - 单击后无法选择其他任何内容

I've got an Excel macro button that upon clicking, exports a number of worksheets in PDF format. VBA below.

我有一个Excel宏按钮,点击后,导出PDF格式的许多工作表。 VBA如下。

The problem is that after the PDF has been exported the document ends up on a different worksheet to where the button is, and I cannot select any objects in the sheet or click anything in the ribbon, but I can select a cell in the sheet and I can move between sheets. (Excel 2007).

问题是,在导出PDF之后,文档最终会出现在按钮所在的不同工作表上,我无法在工作表中选择任何对象或单击功能区中的任何内容,但我可以在工作表中选择一个单元格,我可以在床单之间移动。 (Excel 2007)。

The solution I have discovered is to return to the worksheet where the button is located and click into any cell in that worksheet. Before clicking in any cell the macro button is sort of highlighted - the text is slightly darker than the other macro buttons in the page and there is a faint dotted line around the inside of the button. When I select any cell, the text returns to a normal color and the faint dotted line disappears. I am then able to select objects and use functions in the ribbon.

我发现的解决方案是返回按钮所在的工作表,然后单击该工作表中的任何单元格。在单击任何单元格之前,宏按钮有一些突出显示 - 文本比页面中的其他宏按钮稍暗,并且按钮内部有一条模糊的虚线。当我选择任何单元格时,文本返回正常颜色,淡淡的虚线消失。然后我可以选择对象并使用功能区中的功能。

This is fine for me using the sheet, but as other people in my company will use this I want it to be easy to use. I would like not to have to return to the sheet where the button is to deselect the button, but be able to continue working immediately. I can share screenshots if need be

这对我使用工作表很好,但正如我公司的其他人将使用它我希望它易于使用。我不想返回按钮取消选择按钮的工作表,但能够立即继续工作。如果需要,我可以分享截图

Sub PDFExportAllDashboards()
    Dim myFile As Variant
    Dim strFile As String
    Dim ws As Worksheet
    On Error GoTo errHandler
    Dim arrSheets() As String
    Dim sht As Worksheet
    Dim i As Integer

  Set ws = Worksheets("Dashboard - Focus IT")
  i = 0
  For Each sht In ActiveWorkbook.Worksheets
    If InStr(1, sht.Name, "Dashboard") > 0 Then
      ReDim Preserve arrSheets(i)
      arrSheets(i) = sht.Name
      i = i + 1
    End If
  Next sht

  ThisWorkbook.Sheets(arrSheets).Select

    strFile = Replace(Replace(ws.Name, " ", ""), ".", "_") _
            & "_" _
            & Format(Now(), "yyyy-mm-dd") _
            & ".pdf"
    strFile = ThisWorkbook.Path & "\" & strFile

    myFile = Application.GetSaveAsFilename _
        (InitialFileName:=strFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Save as")

  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                Filename:=myFile, _
                                OpenAfterPublish:=True

 MsgBox "PDF file has been created."

exitHandler:
        Exit Sub
errHandler:
        MsgBox "Could not create PDF file"
        Resume exitHandler
End Sub

1 个解决方案

#1

At the end of your macro (right after - MsgBox "PDF file has been created.") you can complete the same steps that you did manually in VBA

在宏的末尾(紧接着 - MsgBox“PDF文件已创建。”),您可以完成在VBA中手动执行的相同步骤

Return to sheet with:

返回表格:

WORKBOOK_NAME.Sheets("WORKSHEET_NAME").Activate

select a cell:

选择一个单元格:
```
Range("A1").Select 
```

Just fill in the correct workbook and worksheet name

只需填写正确的工作簿和工作表名称即可

#1