make them push a button on exit

[deanl33069]

HI ALL
i this code to save my sheet as a pdf using info from cell c2

Private Sub CommandButton1_Click()


ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:="C:\release" & Range("C2") & ".pdf", _
OpenAfterPublish:=False, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
Quality:=xlQualityStandard, _
From:=1, To:=2
End Sub


I want to make the user push this button before they can exit the program , also how do i prevent it from over writing an existing doc ?

 

[Joe4]

You can check to see if a file already exists using code like this here: https://excel.tips.net/T002516_Determining_If_a_File_Exists.html

What I would recommend doing for your other question is the following:
1. Add a line of code to your print code above that populates some unused (maybe hidden) cell with a value like "Printed".
2. Create a "Workbook_BeforeClose" event procedure code (this is code that runs automatically when they try to close the file) that checks to see if the word "Printed" is in your secret cell.
If it is not, then either call the print code above, or cancel the file close, and post a MsgBox telling the user to do it.
3. If you elect to have the "Workbook_BeforeClose" event procedure code call the print code automatically, then just add a few lines of code under that, removing the word "Print" and re-saving the file (to prep for next time).
Otherwise, you can add code to the "Workbook_Open" event procedure to clear out that secret cell when the workbook is first opened.

See here for information on Workbook_Close: http://spreadsheetpage.com/index.php/tip/handling_the_workbook_beforeclose_event/

 

[deanl33069]

You can check to see if a file already exists using code like this here: https://excel.tips.net/T002516_Determining_If_a_File_Exists.html

What I would recommend doing for your other question is the following:
1. Add a line of code to your print code above that populates some unused (maybe hidden) cell with a value like "Printed".
2. Create a "Workbook_BeforeClose" event procedure code (this is code that runs automatically when they try to close the file) that checks to see if the word "Printed" is in your secret cell.
If it is not, then either call the print code above, or cancel the file close, and post a MsgBox telling the user to do it.
3. If you elect to have the "Workbook_BeforeClose" event procedure code call the print code automatically, then just add a few lines of code under that, removing the word "Print" and re-saving the file (to prep for next time).
Otherwise, you can add code to the "Workbook_Open" event procedure to clear out that secret cell when the workbook is first opened.

See here for information on Workbook_Close: http://spreadsheetpage.com/index.php/tip/handling_the_workbook_beforeclose_event/

not sure if it will work where would i put it ? create a new module or add it to an existing 1?

 

[Joe4]

The "Workbook_BeforeClose" and "Workbook_Open" modules MUST go in the "ThisWorkbook" module, and must be name da a certain way.
If you actually go to that module, you can select them from the drop-down boxes at the top of the VB Editor, and it will ensure that they are named properly.
Take a look at the last link I provided, as that has a lot of good information.

If you use the Function to check to see if the file already exists, that can go in the same module as your existing code. And then you would just add some code to your current procedure to check for the file before creating it.

If you need help putting this all together, please answer the following.
If the button has not already been clicked, would you prefer:
1. A Message Box telling the user to click it
- or -
2. Have the VBA code automatically run it for you

 

[Joe4]

OK, here is what the code looks like to force them to click the Print button.

First, here is the code to put in your original model with your button code currently resides:

Private Sub CommandButton1_Click()

    Dim fName As String
    
'   Set file name
    fName = "C:\release" & Range("C2") & ".pdf"
    
'   Check to see if file name already exists
    If FileThere(fName) Then
        MsgBox "A file by the name of " & fName & " already exists." & vbCrLf & _
            "Please change name in cell C2 and try again.", vbOKOnly, "ERROR!"
        Exit Sub
    Else
'       Print file
        ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            FileName:=fName, _
            OpenAfterPublish:=False, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            Quality:=xlQualityStandard, _
            From:=1, To:=2
'       Record that file is printed
        Range("AZ1") = "Printed"
    End If
        
End Sub


Function FileThere(FileName As String) As Boolean
     FileThere = (Dir(FileName) > "")
End Function

Then, here is code to put in the "ThisWorkbook" module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'   Check to see if file has not been exported before leaving
    If Range("AZ1") <> "Printed" Then
        Cancel = True
        MsgBox "You must click button to export file before leaving", vbOKOnly, "STOP!"
    End If

End Sub


Private Sub Workbook_Open()
'   Clear range AZ1 when opened
    Range("AZ1").ClearContents
End Sub

 

[deanl33069]

TYVM
2 things
I can save the same file over and over again.
when i try to exit using the X i do get the message to click the button but after i click it it still asks me to do over again (loop)

 

[Joe4]

How many sheets are in your workbook?
If multiple, please explain what each one is used for.

 

[deanl33069]

2 sheets
sheet 2 hold tables for drop-down list

 

[Joe4]

So, then the first sheet is the one we are exporting? Is that correct?
Are the sheets just named "Sheet1" and "Sheet2" or something else?

 

[deanl33069]

yes and yes
again TYVM
Dean