Save a file as

[terilad]

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

 

[Corey]

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
Here:
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....

 

[Jacob Skaria]

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]

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

 

[terilad]

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