|You have brought up multiple issues which may require different answers.|
Let's start with this:
This is a file that will be sent out to multiple people to use under their user name on separate computers.
In order for them to use this code, they will have to have macros enabled on their system. This is not something that you can do "remotely" via code since that would be a major security issue. Imagine if I could send you a workbook that included a macro with malicious code along with code that enabled macros on your system without your
permission. That would not be a good thing.
Now, there are tricks that can be used within a workbook to prompt the user to enable macros. One such trick is to hide all worksheets except for one worksheet with a text box that says something like "This workbook is of no use unless macros are enabled. Please close this workbook, enable macros and then re-open this workbook."
Included in the workbook could be a Workbook_Open macro that (assuming macros are enabled) unhides all the important worksheets and hides the worksheet with the text box. With that method, the user will have to make the conscious decision to enable macros if they want to use the workbook.
How would I have it check to see if there is already a file with the same name before saving?
My first question back to you is: Where to want to look for the file? One method for determining if a file exists is to have code that tries to open a file, but with an error handling routine within the code. If the "fileopen" instruction fails because the file does not exist, then the error handler instructions can tell the code what to do next. The thing is, you need to tell the "fileopen" instruction where to look for the file. More on that later, after I address another issue.
Can I set where is saves?
Yes, assuming you know the directory structure of the user's computer. For example, this code sets the path for the export of the PDF and then opens it form the same location. By using CurDir the code uses the default directory that the system is using to save files. You could also build your own string to use as the path, e.g.
strFilePath = "C:\users\ExcelFiles\username\etc"
The following code set the path for saving the file and then for opening it from the same place.
Dim strFileName As String
Dim strFilePath As String
Dim strPdfPath As String
'Set File Path to default user directory
strFilePath = CurDir()
'Use A1 value as filename
strFileName = Range("$A$1")
'Build path to PDF
strPdfPath = strFilePath & "\" & strFileName
Set objShell = CreateObject("Shell.Application")
objShell.Open (strPdfPath & ".pdf")
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.