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]

One last question I forgot to ask, this "Range("C2")", which you are using in the file name being created, is that on Sheet1 or Sheet2?

 

[Joe4]

OK, I have updated the code to be more sheet specific. Depending on your answer to my question above, you may need to change the part in red:

Sheet/General Module code:

Private Sub CommandButton1_Click()

    Dim fName As String
    
'   Set file name
    fName = "C:\release\" & Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").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
        Sheets("Sheet1").Activate
        ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            FileName:=fName, _
            OpenAfterPublish:=False, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            Quality:=xlQualityStandard, _
            From:=1, To:=2
'       Record that file is printed
        Sheets("Sheet2").Range("AZ1") = "Printed"
    End If
        
End Sub


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

ThisWorkbook Module code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'   Check to see if file has not been exported before leaving
    If Sheets("Sheet2").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
    Sheets("Sheet2").Range("AZ1").ClearContents
End Sub

 

[deanl33069]

sheet 1

 

[Joe4]

Then the code I posted in my last post should work, as-is.

 

[deanl33069]

Private Sub CommandButton1_Click()


Dim fName As String

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

' Check to see if file name already exists
If FileThere(filename) 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
Sheets("Sheet1").Activate
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=fName, _
OpenAfterPublish:=False, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
Quality:=xlQualityStandard, _
From:=1, To:=2
' Record that file is printed
Sheets("Sheet1").Range("AZ1") = "Printed"
End If


https://1drv.ms/u/s!AqrVlEBa2rE1iVa2DRBEHzZn0zRy

 

[Joe4]

Is there a question in your last post?

 

[deanl33069]

code not working see error...

from link

 

[Joe4]

I cannot see the link. My workplace blocks most file/image sharing sites.

 

[deanl33069]

so when i click on the x it asked me to save but doesn't save at all.
if i click the commandbutton1 i get an error
Compile error
sub function not defined, and the FileThere is highlight in blue
I click ok and it highlights the "private sub commandbutton1_click in yellow
Sheet 1 is the main sheet sheet 2 only contains 2 tables to fill in sheet 1.

Again ty for you patients this matter.....
dean

 

[Joe4]

Compile error
sub function not defined, and the FileThere is highlight in blue

Did you remember to put the FileThere function code in your module?
It is looking for it and cannot find it.

See the code I posted back up in post 5.
You need to have ALL the code in there.