VBA Save

[terilad]

Hi,

I have the following macro that saves as a filename when closed, how can I
also add to the code for the fil to save as when the save icon is clicked as
well along with the close icon.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim strPath As String
Dim strFilename As String
Dim userResponse As Variant
strFilename = "Galashiels Resources WC " & Format(Sheets("Galashiels
Resources").Range("N2"), "dd-mmm-yy")
userResponse = MsgBox("Do you want to save as " & strFilename, vbYesNo +
vbDefaultButton2 + vbQuestion, "Galashiels Operational Resources © MN ")
If userResponse = vbYes Then
strPath = "C:\Users\Mark\Desktop"
strFilename = strPath & "\" & strFilename & ".xls"
ThisWorkbook.SaveAs Filename:=strFilename, FileFormat:=xlNormal,
CreateBackup:=False
End If
End Sub

Many thanks

Mark

 

[Bob Phillips]

Use the BeforeSave event, make sure that you set Cancel = True

 

[terilad]

Hi Bob,

This is my code I have input.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strPath As String
Dim strFilename As String
Dim userResponse As Variant
strFilename = "Galashiels Resources WC " & Format(Sheets("Galashiels
Resources").Range("N2"), "dd-mmm-yy")
userResponse = MsgBox("Do you want to save as " & strFilename, vbYesNo +
vbCritical, "Galashiels Operational Resources © MN ")
If userResponse = vbYes Then
strPath = "C:\Users\Mark\Desktop"
strFilename = strPath & "\" & strFilename & ".xls"
ThisWorkbook.SaveAs Filename:=strFilename, FileFormat:=xlNormal,
CreateBackup:=False
End If
End Sub

Before save event, when I click on save it pops up the msg box do you want
to save as, when I click yes it pops up again and excel stops working, do you
have any ideas if my code is wrong, also I didn't understand the part set
Cancel = True can you explain this part.

Regards


Mark

 

[Dave Peterson]

Cancel = true
means that you don't want excel to actually process the Save that the user
started when he/she clicked the save button.

If you don't cancel that process, then excel will want to save the file, too.

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strPath As String
Dim strFilename As String
Dim userResponse As Variant

strFilename = "Galashiels Resources WC " _
& Format(Me.Sheets("Galashiels Resources") _
.Range("N2").Value, "dd-mmm-yy")

userResponse = MsgBox(prompt:="Do you want to save as " & strFilename, _
Buttons:=vbYesNo + vbCritical, _
Title:="Galashiels Operational Resources © MN")

If userResponse = vbYes Then
Cancel = True
strPath = "C:\Users\Mark\Desktop"
strFilename = strPath & "\" & strFilename & ".xls"
'stop the _beforesave event from firing because
'of the .saveas
Application.EnableEvents = False
On Error Resume Next
Me.SaveAs Filename:=strFilename, _
FileFormat:=xlNormal, _
CreateBackup:=False
If Err.Number <> 0 Then
MsgBox "SaveAs failed" & vblf & err.Number & vblf & err.Description
Err.Clear
End If
On Error GoTo 0
'start monitoring events again
Application.EnableEvents = False
End If

End Sub

Compiled, but untested.

I also qualified the sheet so that it refered to the workbook being saved. This
code is in the ThisWorkbook module, so the Me keyword refers to that workbook.
(I also changed the .saveas line to use Me instead of ThisWorkbook.)

And the bigger change is the .enableevents line. This stops the .SaveAs from
starting the _beforeSave event.

I also added a check to see if the .saveas was successful. If the filename
wasn't valid, the save may not work and bad things could happen.