Home

yes no Selection button on a msgbox

Mhiltondream
Occasional Visitor

i need a msgbox says "Are you sure that you need to finalize & ws.name" vbyesno
1-if no then do nothing
2-if yes and the folder ws.name is not found then creat
3- if yes and the folder ws.name is found then msgbox says " ws.name" is already found would you like to overwrite it
4- If no do nothing

5-if yes then overwrite


thanks

----------------------------------------------------------------------------------------

Private Sub CommandButton6_Click()
Dim ws As Worksheet
Set ws = Sheets(Sheets.Count)
Dim sPath
sPath = ("\\10.218.165.35\Project Managment\Correspondence\Technical Correspondence\Client-MOD\Invoices\")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'step1 create main folder
If Dir(sPath & ws.Name, vbDirectory) = Empty Then MkDir sPath & ws.Name
Dim sMain As String
sMain = sPath & ws.Name & "\"
'step2 save last sheet as pdf
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sMain & ws.Name, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
'step3 save last sheet as new file/workbook
ActiveWorkbook.Sheets(Sheets.Count).Copy
With ActiveWorkbook
.SaveAs sMain & ws.Name, 51 '<< '51=xlsx, 52=xlsm, 56=xls
.Close
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

------------------------