Excel VBA How to prevent user from hitting cancel in msoFileDialogSaveAs

Question

I am very new to VBA and I am creating a template for my boss. I want to force users to "save as" so that they don't overwrite the template. In other words, I'd like to disable the cancel button when the save as dialog box pops up.

Here is my code:

Dim fPth As Object
Set fPth = Application.FileDialog(msoFileDialogSaveAs)

With fPth
    .InitialFileName = CTAPath
    .InitialFileName = CTAName & "_CAP DATA"
    .Title = "Save with your CTA file"
    .InitialView = msoFileDialogViewList
    .FilterIndex = 2
    If .Show = -1 Then
        .Execute

    End If
End With

I'm thinking I should create an ELSE statement within the IF statement but I can't figure out what it should be. I've tried searching and I'm not coming up with any solutions.

Thank you!

You know, user can overwrite the existing file using the "Save As" dialog too! — ashleedawg, Jul 17 '18 at 11:35
Put your template in a ReadOnly directory and let users open it from there, you can't force users when VBA is disabled — EvR, Jul 17 '18 at 11:37
Excellent idea - protect the file with the settings meant to protect the file, rather than trying to outsmart the users. (Users are really good at finding a way around your best planning.) Although I was also wondering **why do you give the user a dialog box at all**, instead of saving the file where/how you want it to be saved programmatically, and then *informing* the user that the file has been saved (instead of *asking*). Are you aware that you can save without using the `Save As` Dialog at all? — ashleedawg, Jul 17 '18 at 11:40
Grant the template specific permissions to avoid unautorized users to writing it. Its not a coding issue (should not be), I would go to other [Super user](https://superuser.com/) site. — Cleptus, Jul 17 '18 at 11:42

score 2 · Answer 1 · answered Jul 17 '18 at 11:32

2

That's not the way to do this: if the users choose the file itself in the "Save as" filelist, you might end up in the same situation.
I'd advise you to make the file read-only, so nobody can change or overwrite it.

answered Jul 17 '18 at 11:32

Dominique

16,450
15
56
112

Maciej Los · Accepted Answer · 2018-07-17T11:45:52.727

1

I'm not sure you're able to disable Cancel button, but there is workaround...

You can loop .Show method till user hits Save button ;)

For example:

Sub PreventCancel()

Dim fPth As Object
Set fPth = Application.FileDialog(msoFileDialogSaveAs)
Dim result As Variant

With fPth
    .InitialFileName = CTAPath
    .InitialFileName = CTAName & "_CAP DATA"
    .Title = "Save with your CTA file"
    .InitialView = msoFileDialogViewList
    .FilterIndex = 2
    Do
        result = .Show
    Loop While result <> True
   .Execute
End With

End Sub

[EDIT]

I'd suggest to use Application.GetSaveAsFilename Method (Excel) instead of FileDialog, because it gives you more control over it.

Please, read valuable comments to your question also.

edited Jul 17 '18 at 11:45

answered Jul 17 '18 at 11:32

Maciej Los

8,468
1
20
35

1

I think you meant the "save" button, not the cancel button. – ashleedawg Jul 17 '18 at 11:33
1

Just a suggestion: if the goal is to prevent the file from being overwritten, it could be a good idea to not only make sure that the user doesn't click `cancel`, but also that the filename they select is **not the existing filename**. (They can easily still overwrite a file through the Save As dialog!) – ashleedawg Jul 17 '18 at 11:55
@ashleedawg, good idea. I've mentioned already that OP needs to read valuable comments to he's question. – Maciej Los Jul 17 '18 at 11:57

Excel VBA How to prevent user from hitting cancel in msoFileDialogSaveAs

2 Answers2