Excel VBA run-time error - PDF creation macro

[BOOTCAMP55]

I wrote a VBA macro that generates, saves, and opens a PDF of an array of sheets from an Excel workbook. The macro works for most users in my organization, but a few users have reported receiving an error message that states, "Run-time error '5': Invalid procedure call or argument."

All users are on Windows 7. Some users are on Excel 2007 and some are on Excel 2010. Users on both Excel 2007 and Excel 2010 have had success using the macro. Users who receive the run-time error message have success running the macro if they log in to a different computer, so the issue seems to be with the computers and not the users.

I am relatively new to Excel VBA, so this may just be a coding error on my part, but I'm hoping an expert may be able to identify whether or not there is something in my code that could be causing the problem under a certain set of circumstances.

The following is the VBA code for my macro. Please note that the bold section is where the debugger identified the run-time error.

Sub PDF_GENERATION()
'
' PDF_GENERATION Macro
' This macro generates a PDF that includes: YEARLY SUMMARY, GRAPHS, JANUARY, FEBRUARY, MARCH, APRIL, MAY, JUNE, JULY, AUGUST, SEPTEMBER, OCTOBER, NOVEMBER, DECEMBER
'
'
    Application.ScreenUpdating = False
    Sheets(Array("YEARLY SUMMARY", "GRAPHS", "JANUARY", "FEBRUARY", "MARCH", "APRIL", "MAY", "JUNE", "JULY", "AUGUST", "SEPTEMBER", "OCTOBER", "NOVEMBER", "DECEMBER")).Select
    Sheets("YEARLY SUMMARY").Activate
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\users\" & Worksheets("Control Panel").Range("F2").Value & "\Desktop\IV Team Statistics - " & Worksheets("Control Panel").Range("C8").Value & " " & Worksheets("Control Panel").Range("C10").Value & ".PDF" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=True, OpenAfterPublish:=False
    Sheets("Control Panel").Select
    Application.ScreenUpdating = True
End Sub

To obtain the correct filepath for users, I used a function to query the system for the username. Below is the UserName function.

Public Function UserName()
UserName = Environ$("username")
End Function

In the specified filepath in the PDF_GENERATION macro, the portion "Worksheets("Control Panel").Range("F2").Value " is a cell which calls the UserName() function. The filepath when the macro works is C:\Users\(Active Username)\Desktop\IV Team Statistics - (Current Month) (Current Year).PDF

One other note that may or may not have any relevance is in regard to how the macro shows up when I bring up the list of all macros on the developer tab. For instance, I have a macro that unlocks all sheets called DASHBOARD_UNLOCK. On the macro list, that simply shows up as DASHBOARD_UNLOCK. However, for the PDF_GENERATION macro, it shows up on the list as 'Filename'!PDF_GENERATION.PDF_GENERATION. Is there a reason why this is showing up differently? Is that a problem? How would I fix the problem?

Any help on resolving the run-time error would be greatly appreciated. I'm happy to provide more information if there is anything important that I did not include in this post.

Any feedback would be greatly appreciated. Thank you.

 

[AFPathfinder]

Hi, BOOTCAMP55,

I believe the RT error you are receiving can be caused caused by library References issues. Do all users have the Acrobat addin for Excel? I've also run into developing something in Excel 2010 and users with Excel 2007 show missing References because the reference doesn't retro back to a previous version.

I don't have any experience with ".ExportAsFixedFormat", but the macro name looks correct to me assuming your module is also named "PDF_GENERATION".

 

[RoryA]

Welcome to the forum.

The reason the macro shows up that way is simply because you have named the module and macro the same. (That is generally regarded as bad practice)

Regarding the PDF problem, the machines with 2007 must have either at least Service Pack 1 installed, or have the (free) add-in to allow exporting as PDF. If they don't, the code would cause an error.