Home

yes no Selection button on a msgbox

%3CLINGO-SUB%20id%3D%22lingo-sub-291136%22%20slang%3D%22en-US%22%3Eyes%20no%20Selection%20button%20on%20a%20msgbox%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-291136%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20need%20a%20msgbox%20says%20%22Are%20you%20sure%20that%20you%20need%20to%20finalize%20%26amp%3B%20ws.name%22%20vbyesno%3CBR%20%2F%3E1-if%20no%20then%20do%20nothing%3CBR%20%2F%3E2-if%20yes%20and%20the%20folder%20ws.name%20is%20not%20found%20then%20creat%3CBR%20%2F%3E3-%20if%20yes%20and%20the%20folder%20ws.name%20is%20found%20then%20msgbox%20says%20%22%20ws.name%22%20is%20already%20found%20would%20you%20like%20to%20overwrite%20it%3CBR%20%2F%3E4-%20If%20no%20do%20nothing%3CBR%20%2F%3E%3CBR%20%2F%3E5-if%20yes%20then%20overwrite%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3Ethanks%3CBR%20%2F%3E%3CBR%20%2F%3E----------------------------------------------------------------------------------------%3CBR%20%2F%3E%3CBR%20%2F%3EPrivate%20Sub%20CommandButton6_Click()%3CBR%20%2F%3EDim%20ws%20As%20Worksheet%3CBR%20%2F%3ESet%20ws%20%3D%20Sheets(Sheets.Count)%3CBR%20%2F%3EDim%20sPath%3CBR%20%2F%3EsPath%20%3D%20(%22%5C%5C10.218.165.35%5CProject%20Managment%5CCorrespondence%5CTechnical%20Correspondence%5CClient-MOD%5CInvoices%5C%22)%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3EApplication.DisplayAlerts%20%3D%20False%3CBR%20%2F%3E'step1%20create%20main%20folder%3CBR%20%2F%3EIf%20Dir(sPath%20%26amp%3B%20ws.Name%2C%20vbDirectory)%20%3D%20Empty%20Then%20MkDir%20sPath%20%26amp%3B%20ws.Name%3CBR%20%2F%3EDim%20sMain%20As%20String%3CBR%20%2F%3EsMain%20%3D%20sPath%20%26amp%3B%20ws.Name%20%26amp%3B%20%22%5C%22%3CBR%20%2F%3E'step2%20save%20last%20sheet%20as%20pdf%3CBR%20%2F%3Ews.ExportAsFixedFormat%20Type%3A%3DxlTypePDF%2C%20Filename%3A%3D%20_%3CBR%20%2F%3EsMain%20%26amp%3B%20ws.Name%2C%20Quality%3A%3DxlQualityStandard%2C%20IncludeDocProperties%3A%3DTrue%2C%20_%3CBR%20%2F%3EIgnorePrintAreas%3A%3DFalse%2C%20OpenAfterPublish%3A%3DFalse%3CBR%20%2F%3E'step3%20save%20last%20sheet%20as%20new%20file%2Fworkbook%3CBR%20%2F%3EActiveWorkbook.Sheets(Sheets.Count).Copy%3CBR%20%2F%3EWith%20ActiveWorkbook%3CBR%20%2F%3E.SaveAs%20sMain%20%26amp%3B%20ws.Name%2C%2051%20'%26lt%3B%26lt%3B%20'51%3Dxlsx%2C%2052%3Dxlsm%2C%2056%3Dxls%3CBR%20%2F%3E.Close%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3EApplication.DisplayAlerts%20%3D%20True%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3E------------------------%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-291136%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Emsgbox%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EVba%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Evisual%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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

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

Related Conversations