VBA Save PDF file of each sheet from Dashboard

tlc53

Active Member
Joined
Jul 26, 2018
Messages
399
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Is the checkbox a Form Control or is it an ActiveX Control?


e84c090eed5e982ea185a8dd4f1c7466.jpg





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"
 
Upvote 0
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.
 
Upvote 0
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"


Code:
[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.
 
Upvote 0
Solution
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 :LOL:
Really appreciate your help.
 
Upvote 0
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 :LOL:
Really appreciate your help.

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

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top