VBA Save PDF file of each sheet from Dashboard

[tlc53]

Hi,
In my Dashboard sheet I have totals that feed through from each of the client sheets. Next to these totals, in column G, I have check boxes. I would like a saving macro to run if the check box is ticked.

So;

Sheet1 (Dashboard)
Check box ticked - save pdf file of Sheet4 (Cordis)
Check box ticked - save pdf file of Sheet10 (FourPoints)
Etc.

I would like to put the entire file path name where it needs to be saved, in a single cell. This can be located on the Dashboard or on the individual client sheets (whichever is easier).

To be honest, I'm not too sure where to start. Bit of a big ask but can anyone help me with this please? Thank you.

 

[DanteAmor]

Is the checkbox a Form Control or is it an ActiveX Control?

In a cell is the full name of the file path.


In another cell are you going to put the name of the file?


They suggested a cell to the folder and in another cell the name of the file.
Both data on the sheet "Dashboard"

 

[tlc53]

Hi Dante,
At the moment it's a Form Control check box but if I need to, I can change it to an ActiveX (which ever is easier to run/assign the code to). I'm guessing ActiveX is probably more suited?
I was going to put the full file name and path in one cell - for example C:\Documents\Hotels\June 2019\Cordis Inv. 12345
However, I can easily put the path in one cell and the name in another. This is going to be formulated so that the user has no control. They just need to check the box and then a window comes up saying the file has been saved.
The file name and path can be contained in a cell on the Dashboard. Say J12 and J13 (if keeping path and name separate).
Thank you.

 

[DanteAmor]

Do the following, create two ActiveX Control checkboxes, put the folder in J12 and the name in J13.
Put the following code in the events of your sheet "Dashboard"

[COLOR=#0000ff]Private Sub CheckBox1_Click()[/COLOR]
  If CheckBox1 Then
    Call SavePdf("[COLOR=#008000]Cordis[/COLOR]")
    CheckBox1.Value = False
  End If
End Sub


[COLOR=#0000ff]Private Sub CheckBox2_Click()[/COLOR]
  If CheckBox2 Then
    Call SavePdf("[COLOR=#008000]FourPoints[/COLOR]")
    CheckBox2.Value = False
  End If
End Sub


[COLOR=#0000ff]Sub SavePdf(wSheet As String)[/COLOR]
  Dim wfolder As String, wfile As String
  wfolder = Range("[COLOR=#ff0000]J12[/COLOR]").Value
  wfile = Range("[COLOR=#ff0000]J13[/COLOR]").Value
  If wfolder = "" Then
    MsgBox "Enter folder"
    Exit Sub
  End If
  If wfile = "" Then
    MsgBox "Enter file name"
    Exit Sub
  End If
  If Dir(wfolder, vbDirectory) = "" Then
    MsgBox "Folder does not exists"
    Exit Sub
  End If
  If Right(wfolder, 1) <> "\" Then wfolder = wfolder & "\"
  If Right(wfile, 4) <> ".pdf" Then wfile = wfile & ".pdf"
  Sheets(wSheet).ExportAsFixedFormat Type:=xlTypePDF, Filename:=wfolder & wfile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  MsgBox "The file has been saved"
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[tlc53]

Lovely! Thank you Dante.
It would have taken me forever to try and piece it all together and it still probably wouldn't have worked
Really appreciate your help.

 

[DanteAmor]

Lovely! Thank you Dante.
It would have taken me forever to try and piece it all together and it still probably wouldn't have worked
Really appreciate your help.

I'm glad to help you. Thanks for the feedback.