Excel VBA Button - After clicking cannot select anything else

Question

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

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).

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

If you open the VBE while you run the code, does the code finish running completely or not? If yes, what if you add a .Range(Some cell).Select at the end of your macro and see what that does? — David G

Nate May Nate May · Accepted Answer · 2015-07-24T18:17:49

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

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

Excel VBA Button - After clicking cannot select anything else

1 Answers