excel beforesave function

[ErinJ]

I have the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
Application.DisplayAlerts = False
Dim StrPath
Select Case Range("M5").Value
    Case ""
        StrPath = "S:\Approved blank admin forms\"
    Case "Tommy"
        StrPath = "Z:\"
    Case "Brad"
        StrPath = "X:\"
    Case "Erin"
        StrPath = "V:\JobCost\"
    Case "Blake"
        StrPath = "U:\"
    Case "Cody"
        StrPath = "T:\"
    Case "Eric"
        StrPath = "R:\"
    Case Else
End Select
ActiveWorkbook.SaveAs Filename:=StrPath & Sheet1.Range("G8") & ".xls"
Application.DisplayAlerts = True
Range("D5:G5").Select
End Sub

It works great if user goes to file/saveas.
BUT if user goes directly to save it crashes excel (Microsoft Excel has stopped working....trying to recover....closing excel.....reopens in recover mode)
Any thoughts

 

[VoG]

Try adding the lines in red

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
Application.DisplayAlerts = False
Dim StrPath
Select Case Range("M5").Value
    Case ""
        StrPath = "S:\Approved blank admin forms\"
    Case "Tommy"
        StrPath = "Z:\"
    Case "Brad"
        StrPath = "X:\"
    Case "Erin"
        StrPath = "V:\JobCost\"
    Case "Blake"
        StrPath = "U:\"
    Case "Cody"
        StrPath = "T:\"
    Case "Eric"
        StrPath = "R:\"
    Case Else
End Select
Cancel = True
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=StrPath & Sheet1.Range("G8") & ".xls"
Application.EnableEvents = True
Application.DisplayAlerts = True
Range("D5:G5").Select
End Sub

 

[ErinJ]

Vog - That worked thank you. It is almost perfect. I don't like my work around using the case blank. Do you have any suggestions?

Cell M5 tells it the directory and G8 tells it the name but if I am working in the master file I have to have the master file name in G8. I'd like it to be blank. Any thoughts?

 

[ErinJ]

Found a couple of problems I can't figure out.
1. after it has been saved if I try to change the UserID (M5) it doesn't know what to do so it opens the file save as window and I have to tell it where to save it. Okay for me but other users may have a problem with it.

2. After it has been saved when I go to close it and it again ask me to save.
Click yes and it goes into a loop saying file exist ....save over?Yes. Would you like to save. Yes. file exsit....save over yes etc.
Click No and it closes and doesn't save (like it should)
Click Cancel and debug window opens

I'd like it if I said yes it just saved it no matter what even over existing file.
Any thoughts?

 

[VoG]

For 1 maybe

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
Dim StrPath
Select Case Sheet1.Range("M5").Value
    Case ""
        StrPath = "S:\Approved blank admin forms\"
    Case "Tommy"
        StrPath = "Z:\"
    Case "Brad"
        StrPath = "X:\"
    Case "Erin"
        StrPath = "V:\JobCost\"
    Case "Blake"
        StrPath = "U:\"
    Case "Cody"
        StrPath = "T:\"
    Case "Eric"
        StrPath = "R:\"
    Case Else
End Select
Cancel = True
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=StrPath & Sheet1.Range("G8") & ".xls"
Application.EnableEvents = True
Application.DisplayAlerts = True
Range("D5:G5").Select
End Sub

For 2, I would have expected DisplayAlerts=False to suppress that message.

 

[ErinJ]

okay I added sheet1
and somehow I inadvertently changed
Application.DisplayAlerts = False to True
I changed it back and now I still have the problem except the cancel button doesn't give me debug prompt. So that's better.
Still working on it.....

 

[ErinJ]

I added

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWorkbook.Close False
End Sub

And that worked. It closed with no prompts
But then I changed it to

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWorkbook.Close True
End Sub

and it prompts me to save. I click yes and it saves it then ask again and again

 

[ErinJ]

I personally like the prompt but users complain about it. So I'd rather it just close but save any changes.

 

[VoG]

I think that disabling events should do the trick but in case not

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
    .DisplayAlerts = False
    .EnableEvents = False
End With
With Me
    .Save
    .Saved = True
    .Close True
End With
With Application
    .DisplayAlerts = True
    .EnableEvents = True
End With
End Sub