PDA

View Full Version : Want to use vb YES/NO question box



theta
03-31-2011, 10:04 AM
Hi

I want the user to be prompted with a question box ("Overwrite file") when the following criteria is met :



sXLfile = Left(sXLfile, Len(sXLfile) - 4)
'Do not overwrite if file already exists
If Dir(sXLfile & ".pdf", vbNormal + vbReadOnly + vbHidden) <> "" Then 'The file DOES exist


If they select YES, continue on with the code, if they select no then end sub

Also need it so that if the criteria above is not met (the file does NOT exist) then the following code will run anyway

I do not want to repeat the code for both situations (file does not exist..runs, file does exist but overwrite...runs)



ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sXLfile & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
frmWait.lbxInfo.AddItem "Section sheets printed to PDF"
ActiveWorkbook.Close savechanges:=False
frmWait.Repaint
Application.DisplayAlerts = True


Hoping to learn alot from this. Hesitant to create a new sub or use GoTo, sure there is an elegant method

theta
03-31-2011, 10:32 AM
Does file exist :
- No? Go straight to the activesheet.exportas code
- Yes? Confirm if overwrite, then go to the activesheet.exportas code
(If decline overwrite then close activeworkbook without saving)

mdmackillop
03-31-2011, 10:37 AM
Sub test()
sXLfile = Left(sXLfile, Len(sXLfile) - 4)
'Do not overwrite if file already exists
If Dir(sXLfile & ".pdf", vbNormal + vbReadOnly + vbHidden) <> "" Then 'The file DOES exist
If MsgBox("Overwrite?", vbYesNo + vbQuestion) = vbNo Then Exit Sub
End If
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sXLfile & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
frmWait.lbxInfo.AddItem "Section sheets printed to PDF"
ActiveWorkbook.Close savechanges:=False
frmWait.Repaint
Application.DisplayAlerts = True

End Sub