Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
GetSaveAsFile
I am trying to do the following:
Ask the user when the user is closin the file if the save had been saved. If yes, close the file and quit application. If no, then have the SaveAs dialog box appear with a preset filename and file directory. If the user is OK with filename and directory, the user saves file; application quit. If cancel, exit sub. This is what I have so far If MsgBox("Have you save this file?", vbYesNo) = vbNo Then sName = Range("File").Value & Range("Project").Value & " " & "Insp. Report," & " " & Format(Report(0, 1), "d-mmm-yy") res = Application.GetSaveAsFilename(InitialFileName:=sNa me) ActiveWorkbook.SaveAs Filename:=res Application.Quit Cancel = True Exit Sub Else ThisWorkbook.Saved = True Application.Quit End If -- Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
GetSaveAsFile
Try something like the following:
Sub AAA() Dim FName As Variant Dim MsgResult As VbMsgBoxResult Dim InitFileName As String InitFileName = "C:\Test.xls" '<<<< CHANGE THIS MsgResult = MsgBox("Have you saved the file?", vbYesNoCancel) Select Case MsgResult Case vbNo FName = Application.GetSaveAsFilename( _ InitFileName, "Excel File (*.xls),*.xls") If FName = False Then ' user cancelled from SaveAs dialog. Exit Sub Else With Application .DisplayAlerts = False .EnableEvents = False ActiveWorkbook.SaveAs Filename:=FName .EnableEvents = True .DisplayAlerts = True ' .Quit End With End If Case vbYes ThisWorkbook.Saved = True 'Application.Quit Case vbCancel Exit Sub End Select End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Daviv" wrote in message news:EC7A92C1-179A-4076-B503- ... I am trying to do the following: Ask the user when the user is closin the file if the save had been saved. If yes, close the file and quit application. If no, then have the SaveAs dialog box appear with a preset filename and file directory. If the user is OK with filename and directory, the user saves file; application quit. If cancel, exit sub. This is what I have so far If MsgBox("Have you save this file?", vbYesNo) = vbNo Then sName = Range("File").Value & Range("Project").Value & " " & "Insp. Report," & " " & Format(Report(0, 1), "d-mmm-yy") res = Application.GetSaveAsFilename(InitialFileName:=sNa me) ActiveWorkbook.SaveAs Filename:=res Application.Quit Cancel = True Exit Sub Else ThisWorkbook.Saved = True Application.Quit End If -- Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
GetSaveAsFile
Thanks for the response. I have another question. When I hit the cancel
button for both 1) vbyesnocancel and 2) saveas dialog, I get this message, "Do you want to save the changes you made to "this file". What I want to occur when I hit the cancel button is to exit the sub only. It is cumbersome to hit the "cancel' button again. -- Thanks! "Chip Pearson" wrote: Try something like the following: Sub AAA() Dim FName As Variant Dim MsgResult As VbMsgBoxResult Dim InitFileName As String InitFileName = "C:\Test.xls" '<<<< CHANGE THIS MsgResult = MsgBox("Have you saved the file?", vbYesNoCancel) Select Case MsgResult Case vbNo FName = Application.GetSaveAsFilename( _ InitFileName, "Excel File (*.xls),*.xls") If FName = False Then ' user cancelled from SaveAs dialog. Exit Sub Else With Application .DisplayAlerts = False .EnableEvents = False ActiveWorkbook.SaveAs Filename:=FName .EnableEvents = True .DisplayAlerts = True ' .Quit End With End If Case vbYes ThisWorkbook.Saved = True 'Application.Quit Case vbCancel Exit Sub End Select End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Daviv" wrote in message news:EC7A92C1-179A-4076-B503- ... I am trying to do the following: Ask the user when the user is closin the file if the save had been saved. If yes, close the file and quit application. If no, then have the SaveAs dialog box appear with a preset filename and file directory. If the user is OK with filename and directory, the user saves file; application quit. If cancel, exit sub. This is what I have so far If MsgBox("Have you save this file?", vbYesNo) = vbNo Then sName = Range("File").Value & Range("Project").Value & " " & "Insp. Report," & " " & Format(Report(0, 1), "d-mmm-yy") res = Application.GetSaveAsFilename(InitialFileName:=sNa me) ActiveWorkbook.SaveAs Filename:=res Application.Quit Cancel = True Exit Sub Else ThisWorkbook.Saved = True Application.Quit End If -- Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|