| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to save as
I want the macro to give the user the option (yes or no) after the message of
"Save File As ...". The macro appear below. Any suggestions will be greatly appreciated. Sub cmdExit_Click() Dim Response As String Dim msg As String Dim Style As String Dim sPath As String Dim sFilename As String Dim ans msg = "Are you sure you want to Exit the application and Close Excel?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then sPath = "C:\MetroWest\" sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") ans = MsgBox("Save File As " & sFilename) If ans = vbOK Then ActiveWorkbook.SaveAs sPath & sFilename ActiveWorkbook.Close savechanges:=True Application.Exit Application.StatusBar = "Application Closing." End If Else ActiveWorkbook.Activate End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro to save as
I am not exactly sure what you are trying to do, but here is my interpretation:
Dim Response As String Dim msg As String Dim Style As String Dim sPath As String Dim sFilename As String Dim ans sPath = "C:\MetroWest\" sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") ans = MsgBox("Save File As " & sFilename) If ans = vbOK Then ActiveWorkbook.SaveAs sPath & sFilename End If msg = "Are you sure you want to Exit the application and Close Excel?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then ActiveWorkbook.Close savechanges:=True Application.Exit Application.StatusBar = "Application Closing." Else ActiveWorkbook.Activate End If Let me know if this is not exactly what you were trying to accomplish. -- Happy Coding, Scott "Mo" wrote: I want the macro to give the user the option (yes or no) after the message of "Save File As ...". The macro appear below. Any suggestions will be greatly appreciated. Sub cmdExit_Click() Dim Response As String Dim msg As String Dim Style As String Dim sPath As String Dim sFilename As String Dim ans msg = "Are you sure you want to Exit the application and Close Excel?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then sPath = "C:\MetroWest\" sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") ans = MsgBox("Save File As " & sFilename) If ans = vbOK Then ActiveWorkbook.SaveAs sPath & sFilename ActiveWorkbook.Close savechanges:=True Application.Exit Application.StatusBar = "Application Closing." End If Else ActiveWorkbook.Activate End If End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro to save as
Scott,
Sorry for the vague info, however I figure it out. But, I have two issues with this code, I appreciate any suggestions. (1) I want to save the file based on entries in two seperatae cells, the first being the cell containing text and the second based on date (I have the date part already). (2)After saving, I want the macro to completely exit Excel. Below is the code: Sub cmdExit_Click() Dim Response As String Dim msg As String Dim Style As String Dim sPath As String Dim sFilename As String Dim ans msg = "Are you sure you want to Exit the application and Close Excel?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then sPath = "C:\MetroWest\" sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") msg = "Save File As " & sFilename Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then ActiveWorkbook.SaveAs sPath & sFilename ActiveWorkbook.Close savechanges:=True Application.Exit Application.StatusBar = "Application Closing." End If Else ActiveWorkbook.Activate End If End Sub "Scott Vincent" wrote: I am not exactly sure what you are trying to do, but here is my interpretation: Dim Response As String Dim msg As String Dim Style As String Dim sPath As String Dim sFilename As String Dim ans sPath = "C:\MetroWest\" sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") ans = MsgBox("Save File As " & sFilename) If ans = vbOK Then ActiveWorkbook.SaveAs sPath & sFilename End If msg = "Are you sure you want to Exit the application and Close Excel?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then ActiveWorkbook.Close savechanges:=True Application.Exit Application.StatusBar = "Application Closing." Else ActiveWorkbook.Activate End If Let me know if this is not exactly what you were trying to accomplish. -- Happy Coding, Scott "Mo" wrote: I want the macro to give the user the option (yes or no) after the message of "Save File As ...". The macro appear below. Any suggestions will be greatly appreciated. Sub cmdExit_Click() Dim Response As String Dim msg As String Dim Style As String Dim sPath As String Dim sFilename As String Dim ans msg = "Are you sure you want to Exit the application and Close Excel?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then sPath = "C:\MetroWest\" sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") ans = MsgBox("Save File As " & sFilename) If ans = vbOK Then ActiveWorkbook.SaveAs sPath & sFilename ActiveWorkbook.Close savechanges:=True Application.Exit Application.StatusBar = "Application Closing." End If Else ActiveWorkbook.Activate End If End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro to save as
Is this what you are looking for?
sFilename = Worksheets("MainMenu").Range("C4").Value & " " & Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") Said Another Way: sFilename = Me.Range("C4").Value & " " & Format(Me.Range("C5").Value, "mm-dd-yyyy") Range("C4") is the text portion of the name. How do you validate that the file name is valid? -- Happy Coding, Scott "Mo" wrote: Scott, Sorry for the vague info, however I figure it out. But, I have two issues with this code, I appreciate any suggestions. (1) I want to save the file based on entries in two seperatae cells, the first being the cell containing text and the second based on date (I have the date part already). (2)After saving, I want the macro to completely exit Excel. Below is the code: Sub cmdExit_Click() Dim Response As String Dim msg As String Dim Style As String Dim sPath As String Dim sFilename As String Dim ans msg = "Are you sure you want to Exit the application and Close Excel?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then sPath = "C:\MetroWest\" sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") msg = "Save File As " & sFilename Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then ActiveWorkbook.SaveAs sPath & sFilename ActiveWorkbook.Close savechanges:=True Application.Exit Application.StatusBar = "Application Closing." End If Else ActiveWorkbook.Activate End If End Sub "Scott Vincent" wrote: I am not exactly sure what you are trying to do, but here is my interpretation: Dim Response As String Dim msg As String Dim Style As String Dim sPath As String Dim sFilename As String Dim ans sPath = "C:\MetroWest\" sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") ans = MsgBox("Save File As " & sFilename) If ans = vbOK Then ActiveWorkbook.SaveAs sPath & sFilename End If msg = "Are you sure you want to Exit the application and Close Excel?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then ActiveWorkbook.Close savechanges:=True Application.Exit Application.StatusBar = "Application Closing." Else ActiveWorkbook.Activate End If Let me know if this is not exactly what you were trying to accomplish. -- Happy Coding, Scott "Mo" wrote: I want the macro to give the user the option (yes or no) after the message of "Save File As ...". The macro appear below. Any suggestions will be greatly appreciated. Sub cmdExit_Click() Dim Response As String Dim msg As String Dim Style As String Dim sPath As String Dim sFilename As String Dim ans msg = "Are you sure you want to Exit the application and Close Excel?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then sPath = "C:\MetroWest\" sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") ans = MsgBox("Save File As " & sFilename) If ans = vbOK Then ActiveWorkbook.SaveAs sPath & sFilename ActiveWorkbook.Close savechanges:=True Application.Exit Application.StatusBar = "Application Closing." End If Else ActiveWorkbook.Activate End If End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro to save as
Scott,
Thanks, it's working fine. "C4" is a locked cell. However, it does not completely exits Excel. Mo "Scott Vincent" wrote: Is this what you are looking for? sFilename = Worksheets("MainMenu").Range("C4").Value & " " & Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") Said Another Way: sFilename = Me.Range("C4").Value & " " & Format(Me.Range("C5").Value, "mm-dd-yyyy") Range("C4") is the text portion of the name. How do you validate that the file name is valid? -- Happy Coding, Scott "Mo" wrote: Scott, Sorry for the vague info, however I figure it out. But, I have two issues with this code, I appreciate any suggestions. (1) I want to save the file based on entries in two seperatae cells, the first being the cell containing text and the second based on date (I have the date part already). (2)After saving, I want the macro to completely exit Excel. Below is the code: Sub cmdExit_Click() Dim Response As String Dim msg As String Dim Style As String Dim sPath As String Dim sFilename As String Dim ans msg = "Are you sure you want to Exit the application and Close Excel?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then sPath = "C:\MetroWest\" sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") msg = "Save File As " & sFilename Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then ActiveWorkbook.SaveAs sPath & sFilename ActiveWorkbook.Close savechanges:=True Application.Exit Application.StatusBar = "Application Closing." End If Else ActiveWorkbook.Activate End If End Sub "Scott Vincent" wrote: I am not exactly sure what you are trying to do, but here is my interpretation: Dim Response As String Dim msg As String Dim Style As String Dim sPath As String Dim sFilename As String Dim ans sPath = "C:\MetroWest\" sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") ans = MsgBox("Save File As " & sFilename) If ans = vbOK Then ActiveWorkbook.SaveAs sPath & sFilename End If msg = "Are you sure you want to Exit the application and Close Excel?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then ActiveWorkbook.Close savechanges:=True Application.Exit Application.StatusBar = "Application Closing." Else ActiveWorkbook.Activate End If Let me know if this is not exactly what you were trying to accomplish. -- Happy Coding, Scott "Mo" wrote: I want the macro to give the user the option (yes or no) after the message of "Save File As ...". The macro appear below. Any suggestions will be greatly appreciated. Sub cmdExit_Click() Dim Response As String Dim msg As String Dim Style As String Dim sPath As String Dim sFilename As String Dim ans msg = "Are you sure you want to Exit the application and Close Excel?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then sPath = "C:\MetroWest\" sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") ans = MsgBox("Save File As " & sFilename) If ans = vbOK Then ActiveWorkbook.SaveAs sPath & sFilename ActiveWorkbook.Close savechanges:=True Application.Exit Application.StatusBar = "Application Closing." End If Else ActiveWorkbook.Activate End If End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro to save as
"Mo" wrote: Scott, Thanks, it's working fine. "C4" is a locked cell. However, it does not completely exits Excel. Mo "Scott Vincent" wrote: Is this what you are looking for? sFilename = Worksheets("MainMenu").Range("C4").Value & " " & Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") Said Another Way: sFilename = Me.Range("C4").Value & " " & Format(Me.Range("C5").Value, "mm-dd-yyyy") Range("C4") is the text portion of the name. How do you validate that the file name is valid? -- Happy Coding, Scott "Mo" wrote: Scott, Sorry for the vague info, however I figure it out. But, I have two issues with this code, I appreciate any suggestions. (1) I want to save the file based on entries in two seperatae cells, the first being the cell containing text and the second based on date (I have the date part already). (2)After saving, I want the macro to completely exit Excel. Below is the code: Sub cmdExit_Click() Dim Response As String Dim msg As String Dim Style As String Dim sPath As String Dim sFilename As String Dim ans msg = "Are you sure you want to Exit the application and Close Excel?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then sPath = "C:\MetroWest\" sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") msg = "Save File As " & sFilename Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then ActiveWorkbook.SaveAs sPath & sFilename ActiveWorkbook.Close savechanges:=True Application.Exit Application.StatusBar = "Application Closing." End If Else ActiveWorkbook.Activate End If End Sub "Scott Vincent" wrote: I am not exactly sure what you are trying to do, but here is my interpretation: Dim Response As String Dim msg As String Dim Style As String Dim sPath As String Dim sFilename As String Dim ans sPath = "C:\MetroWest\" sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") ans = MsgBox("Save File As " & sFilename) If ans = vbOK Then ActiveWorkbook.SaveAs sPath & sFilename End If msg = "Are you sure you want to Exit the application and Close Excel?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then ActiveWorkbook.Close savechanges:=True Application.Exit Application.StatusBar = "Application Closing." Else ActiveWorkbook.Activate End If Let me know if this is not exactly what you were trying to accomplish. -- Happy Coding, Scott "Mo" wrote: I want the macro to give the user the option (yes or no) after the message of "Save File As ...". The macro appear below. Any suggestions will be greatly appreciated. Sub cmdExit_Click() Dim Response As String Dim msg As String Dim Style As String Dim sPath As String Dim sFilename As String Dim ans msg = "Are you sure you want to Exit the application and Close Excel?" Style = vbYesNo + vbInformation + vbDefaultButton2 Response = MsgBox(msg, Style) If Response = vbYes Then sPath = "C:\MetroWest\" sFilename = Format(Worksheets("MainMenu").Range("C5").Value, "mm-dd-yyyy") ans = MsgBox("Save File As " & sFilename) If ans = vbOK Then ActiveWorkbook.SaveAs sPath & sFilename ActiveWorkbook.Close savechanges:=True Application.Exit Application.StatusBar = "Application Closing." End If Else ActiveWorkbook.Activate End If End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to Insert Current Date into cell - Macro to "Save As" | Excel Worksheet Functions | |||
| MACRO SAVE HELP | Excel Worksheet Functions | |||
| Macro to Save without the Save Message | Excel Discussion (Misc queries) | |||
| ASP: Open Excel File with Macro, Allow Macro to run, and then save | Excel Programming | |||
| Prompted to save changes after macro save - why? | Excel Programming | |||
Linear Mode
