Results 1 to 6 of 6

Thread: VBA Save PDF file of each sheet from Dashboard

  1. #1
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    308
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Save PDF file of each sheet from Dashboard

    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.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,354
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Save PDF file of each sheet from Dashboard

    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"
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    308
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Save PDF file of each sheet from Dashboard

    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.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,354
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Save PDF file of each sheet from Dashboard

    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:
    Private Sub CheckBox1_Click()
      If CheckBox1 Then
        Call SavePdf("Cordis")
        CheckBox1.Value = False
      End If
    End Sub
    
    
    Private Sub CheckBox2_Click()
      If CheckBox2 Then
        Call SavePdf("FourPoints")
        CheckBox2.Value = False
      End If
    End Sub
    
    
    Sub SavePdf(wSheet As String)
      Dim wfolder As String, wfile As String
      wfolder = Range("J12").Value
      wfile = Range("J13").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.
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    308
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Save PDF file of each sheet from Dashboard

    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.

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,354
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Save PDF file of each sheet from Dashboard

    Quote Originally Posted by tlc53 View Post
    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.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •