make them push a button on exit

deanl33069

Board Regular
Joined
May 2, 2019
Messages
115
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 ?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 2, 2019
Messages
115
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:
Code:
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:
Code:
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

Board Regular
Joined
May 2, 2019
Messages
115
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,136,284
Messages
5,674,836
Members
419,529
Latest member
TommasoP11

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
Top