Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a file as
Can anyone help me with a piece of code to put in place of "Do something if answer is yes" Here is the code: Private Sub Workbook_BeforeClose(Cancel As Boolean) If MsgBox("Do you want to save as 'Galashiels Resources WC " & Range("N2") & _ "'", vbYesNo + vbInformation, "Galashiels Operational Resources © MN ") = vbYes Then 'Do something if answer is yes End If End Sub I am looking for a command to put in the code to replace: 'Do something if answer is yes, I want this to save a file as the new name e.g Resources WC and the range N2 on the desktop, if no is selected I want the workbook to close and make no changes, can you help with this code. Regards Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a file as
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim res As Variant res = MsgBox("Do you want to save as 'Galashiels Resources WC " & Range("N2") & "'", vbYesNo + vbInformation, "Galashiels Operational Resources © MN") 'Do something if answer is yes If res = vbNo Then Application.DisplayAlerts = False ChDir "C:\" ' Change to Suit Path required If Sheet1.Range("N2").Value = "" Then He res = InputBox("What do you want to Name the WorkBook?") If res = "" Then GoTo Here If res < "" Then Sheet1.Range("N2").Value = res End If If ActiveSheet.Range("N2").Value < "" Then ActiveWorkbook.Saveas Filename:="C:\" + Range("N2").Value + ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False ' Also Change to Suit Path If res = vbYes Then Application.DisplayAlerts = False ActiveWorkbook.Close End If End If End Sub Private Sub Workbook_Open() Application.DisplayAlerts = True End Sub Regards Corey.... "terilad" wrote in message ... Can anyone help me with a piece of code to put in place of "Do something if answer is yes" Here is the code: Private Sub Workbook_BeforeClose(Cancel As Boolean) If MsgBox("Do you want to save as 'Galashiels Resources WC " & Range("N2") & _ "'", vbYesNo + vbInformation, "Galashiels Operational Resources © MN ") = vbYes Then 'Do something if answer is yes End If End Sub I am looking for a command to put in the code to replace: 'Do something if answer is yes, I want this to save a file as the new name e.g Resources WC and the range N2 on the desktop, if no is selected I want the workbook to close and make no changes, can you help with this code. Regards Mark |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a file as
Private Sub Workbook_BeforeClose(Cancel As Boolean) If MsgBox("Do you want to save as 'Galashiels Resources WC " & Range("N2") & _ "'", vbYesNo + vbInformation, "Galashiels Operational Resources © MN ") = vbYes Then ActiveWorkbook.SaveAs Filename:= "Galashiels Resources WC " & Range("N2") ActiveWorkbook.Close Else ActiveWorkbook.Close savechanges:=false End If End Sub If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Can anyone help me with a piece of code to put in place of "Do something if answer is yes" Here is the code: Private Sub Workbook_BeforeClose(Cancel As Boolean) If MsgBox("Do you want to save as 'Galashiels Resources WC " & Range("N2") & _ "'", vbYesNo + vbInformation, "Galashiels Operational Resources © MN ") = vbYes Then 'Do something if answer is yes End If End Sub I am looking for a command to put in the code to replace: 'Do something if answer is yes, I want this to save a file as the new name e.g Resources WC and the range N2 on the desktop, if no is selected I want the workbook to close and make no changes, can you help with this code. Regards Mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a file as
Forgot to mention that you need to mention the path..like ActiveWorkbook.SaveAs Filename:= "c:\" & "Galashiels Resources WC " & Range("N2") If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Private Sub Workbook_BeforeClose(Cancel As Boolean) If MsgBox("Do you want to save as 'Galashiels Resources WC " & Range("N2") & _ "'", vbYesNo + vbInformation, "Galashiels Operational Resources © MN ") = vbYes Then ActiveWorkbook.SaveAs Filename:= "Galashiels Resources WC " & Range("N2") ActiveWorkbook.Close Else ActiveWorkbook.Close savechanges:=false End If End Sub If this post helps click Yes --------------- Jacob Skaria "terilad" wrote: Can anyone help me with a piece of code to put in place of "Do something if answer is yes" Here is the code: Private Sub Workbook_BeforeClose(Cancel As Boolean) If MsgBox("Do you want to save as 'Galashiels Resources WC " & Range("N2") & _ "'", vbYesNo + vbInformation, "Galashiels Operational Resources © MN ") = vbYes Then 'Do something if answer is yes End If End Sub I am looking for a command to put in the code to replace: 'Do something if answer is yes, I want this to save a file as the new name e.g Resources WC and the range N2 on the desktop, if no is selected I want the workbook to close and make no changes, can you help with this code. Regards Mark |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a file as
Hi Corey, I seem to be getting an error at this line in the code: FileFormat:= xlOpenXLMWorkbook, CreateBackup:=False Compile error Syntax error. I am using this code with excel 97 - 2003 Any ideas what i'm doing wrong?? Regards Mark "Corey" wrote: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim res As Variant res = MsgBox("Do you want to save as 'Galashiels Resources WC " & Range("N2") & "'", vbYesNo + vbInformation, "Galashiels Operational Resources © MN") 'Do something if answer is yes If res = vbNo Then Application.DisplayAlerts = False ChDir "C:\" ' Change to Suit Path required If Sheet1.Range("N2").Value = "" Then He res = InputBox("What do you want to Name the WorkBook?") If res = "" Then GoTo Here If res < "" Then Sheet1.Range("N2").Value = res End If If ActiveSheet.Range("N2").Value < "" Then ActiveWorkbook.Saveas Filename:="C:\" + Range("N2").Value + ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False ' Also Change to Suit Path If res = vbYes Then Application.DisplayAlerts = False ActiveWorkbook.Close End If End If End Sub Private Sub Workbook_Open() Application.DisplayAlerts = True End Sub Regards Corey.... "terilad" wrote in message ... Can anyone help me with a piece of code to put in place of "Do something if answer is yes" Here is the code: Private Sub Workbook_BeforeClose(Cancel As Boolean) If MsgBox("Do you want to save as 'Galashiels Resources WC " & Range("N2") & _ "'", vbYesNo + vbInformation, "Galashiels Operational Resources © MN ") = vbYes Then 'Do something if answer is yes End If End Sub I am looking for a command to put in the code to replace: 'Do something if answer is yes, I want this to save a file as the new name e.g Resources WC and the range N2 on the desktop, if no is selected I want the workbook to close and make no changes, can you help with this code. Regards Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SAVE and SAVE AS options disappeared from the drop down FILE menu | Excel Discussion (Misc queries) | |||
Excell2003 (SP-1) File > Save and File > Save As.. grayed out | Excel Discussion (Misc queries) | |||
Save Excel file - prompts to save - no Volitile functions used | Excel Worksheet Functions | |||
Excel marcos firing on file save as but not file save | Excel Programming | |||
Save File to Another Directory, but not change Users File Save location | Excel Programming |