Save a file as

T

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
 
C

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....
 
J

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
 
J

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
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Save 3
BeforeSave Event 5
Remove Identical words 0
VBA 4
BeforeSave 7
Save as 13
Making a macro only work on certain files 2
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0

Top