VBA code for "Save As" dialog box and PDF File format

[DAD]

Hi Ladies and Gents,

I am very new at VBA coding in excel, so please excuse my lack of technical literacy.

I am trying to write a simple VBA script in my excel spreadsheet and link it to a button so when pressed, the "Save As" dialog box opens and the "Format" defaults to PDF. That is the main objective of the script. I am also interested to know if it is possible to insert words (from two cells in the spreadsheet) into the "File Name" section of the "Save As" dialog box?

The reason I don't want to run an export as PDF script as I have need in other threads in the forum, is that many colleagues will be running this spreadsheet, and they need the ability to save the files wherever they desire on their machine.

I hope my query is clear and contains enough info.

Thanks in advance for any help.

 

[DAD]

Hi Mike,

Thanks again, it is now almost perfect. The only problem I have now is that the workbook has 6 worksheets. When I execute the code, it saves all 6 workouts as separate files. Is there a way to just save the active worksheet, not all 6?

The code I have thus far is:

Sub Save_as_PDF()

Dim strAA7 As String, strL7 As String, strFileName As String

strL7 = Range("L7").Value
strAA7 = Range("AA7").Value
strFileName = strL7 & "-" & strAA7


Application.Dialogs(xlDialogSaveAs).Show "" & strFileName, 46


End Sub


Thanks again for your help, it is very much appreciated.

 

[mikerickson]

Towards the bottom of the dialog, there is an option button to select Save Workbook or Worksheet.
I don't know how to set that button to default to Worksheet