Auto Save File

[MarkReddell]

I need to Auto. save my Excel File to my Customer's name in B4; however if there is no entry in b4; I need a message box to prompt me toenter a name before save file!!! Im using this code:

(Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
MsgBox ("This File is Being Saved in the Customer's Name & to the MSN SkyDrive Web-site In The Business Folder! Goodbye For Now")
ActiveWorkbook.SaveAs Filename:="https://ptkmnm.docs.live.net/a7f2fe19067884e3/BUSINESS/" & Sheets("LOAN SCENARIOS").Range("B4").Value & ".xlsm"
End Sub)

THANX FOR ANY HELP!!!

 

[Joe4]

Try this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
    Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
    MsgBox ("This File is Being Saved in the Customer's Name & to the MSN SkyDrive Web-site In The Business Folder! Goodbye For Now")
 
    If Len(Range("B4")) = 0 Then
        Cancel = True
        MsgBox "You must enter a name in cell B4 and try again!"
    Else
        ActiveWorkbook.SaveAs Filename:="[URL]https://ptkmnm.docs.live.net/a7f2fe19067884e3/BUSINESS/[/URL]" & Sheets("LOAN SCENARIOS").Range("B4").Value & ".xlsm"
    End If
 
End Sub

 

[MarkReddell]

Thanx Joe4 "4" your help!!! It's a blessing 2 have help out there!!! God's Blessings 2 U & family in the CHRISTmas Season!!!

 

[Joe4]

Thank you for the kind words. Right back at you!

 

[MarkReddell]

Hey Joe4, Thanx for your reply!!! How would I insert a pause in this code after i goto "B4" and wait for an entry, then continue the Macro to save & close the file in the customer's name? (Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
MsgBox ("This File is Being Saved in the Customer's Name & to the MSN SkyDrive Web-site In The Business Folder! Goodbye For Now")

If Len(Range("B4")) = 0 Then
Cancel = True
MsgBox "You must enter a name in cell B4 and try again!"
Else
ActiveWorkbook.SaveAs Filename:="https://ptkmnm.docs.live.net/a7f2fe19067884e3/BUSINESS/" & Sheets("LOAN SCENARIOS").Range("B4").Value & ".xlsm"
End If

End Sub)

 

[Joe4]

If you wanted to do something like that, I would approach it a bit differently. I would have the Macro prompt the user for an entry (using an Input Box) and then you can populate B4 with the value of this Input Box and continue with your macro.