• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 16
  • Last Modified:

Excel VBA, If User Doesn't Save File Elsewhere, Then Exit Workbook

Hello,

I have the following code:

If ActiveWorkbook.Path <> "S:\.Cerberus Publication" Then
'Do Nothing
Else
MsgBox ("Save File on YOUR PC; You Cannot Use S:\ Drive Copy.")
Application.Dialogs(xlDialogSaveAs).Show ("*.xlsb")

Open in new window


What do I need to add to it in orderto make it exit the workbook if someone exits the file save as dialog that appears? Also, where should I put this code so it fires up when the workbook is opened.
0
EnigmaMatter
Asked:
EnigmaMatter
  • 3
  • 2
1 Solution
 
Rgonzo1971Commented:
Hi,

Place in the ThisWorkbook module

Private Sub Workbook_Open()
If ActiveWorkbook.Path <> "S:\.Cerberus Publication" Then
'Do Nothing
Else
MsgBox ("Save File on YOUR PC; You Cannot Use S:\ Drive Copy.")
res = Application.GetSaveAsFilename(InitialFileName:=".xlsb", _
        FileFilter:="Excel Binary files (*.xlsb), *.xlsb, All files (*.*), *.*")

If res = 0 Or res Like "S:\*" Then
    Thisworbook.Close False
Else
    ThisWorkbook.SaveAs res
End If
End Sub

Open in new window

Regards
0
 
EnigmaMatterAuthor Commented:
Hello,

If I click cancel, it errors on Line 10.

Also, it seems to be missing an End If?
0
 
EnigmaMatterAuthor Commented:
I added in the End If and corrected the typo. It works! Thanks!
0
 
Rgonzo1971Commented:
Sorry

replace line 10 with
ThisWorkBook.Close False
0
 
EnigmaMatterAuthor Commented:
There needs to be another End If at the end (before End Sub), thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now