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!

Answer 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.