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 ?

 

[deanl33069]

I think i have copied everything right .
when i click the X i get
You must click button to export file before leaving
I click OK
and press the button and get this error
Run time error 1004
the document may be opened or an error may have been countered when saving
I press the debug and get the statement of code the lines




ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=fName, _
OpenAfterPublish:=False, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
Quality:=xlQualityStandard, _
From:=1, To:=2
are highlighted in yellow and an arrow ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _
FileName:=fName, _
OpenAfterPublish:=False, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
Quality:=xlQualityStandard, _
From:=1, To:=2

and the
From:=1, to :=2 has an arrow pointed at it

 

[Joe4]

I do not know that I can do much more without seeing your full code and possibly having access to your file.
If you want to upload it to a file sharing site and providing a link, I can download it when I am at home later tonight.

 

[deanl33069]

again tyvm
https://1drv.ms/u/s!AqrVlEBa2rE1imm27K1oE9bl8Cdm?e=qi04HS

 

[deanl33069]

i did sednd u a link , hope it works . I have tried to edit the code but no luck.

 

[Joe4]

I am looking at your code, and it looks like you did not make the changes I showed you back in post 12 to account for that fact that you have multiple sheets (and you commented out most of the Workbook_BeforeClose code (including the "End Sub" line, though you did not comment out the "Private Sub" line, which will cause errors.

Fix those issues. There are things you can do to help you catch some of these syntax errors.

1. Turn on "Option Explicit", which will force you to declare all variables before using them. This helps catch typos.
Here is an article on that: https://www.excel-easy.com/vba/examples/option-explicit.html

2. Before testing out your code, compile it first. This will let you know of any blatant errors which will crash your program, like having a "Private Sub..." line with no matching "End Sub" line.
To do that, from the VB Editor, go to the Debug menu and select "Compile VBAProject".
Always do this first! There is no point in even attempting to run your code until you compile it and get no errors!

 

[deanl33069]

thank you so much for your help
This is my first attempt at anything like this. I have always been a network admin. only had to deal with login and printer scripts.
This is a WHOLE different world....isn't it?