Prevent to modify file extension

[amrane]

Dear Forum,

I want to protect some file xlsb (equipped vba code) to be saved as xlsx file, I did may search in the network, but looks the below code has inconvenient to produce endless message loops, can you support to solve this problem:

thank you sir in advance for your support

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim FD As FileDialog, FTyp As Long
Dim folderpath As String, MyWbName As String

    Cancel = True

folderpath = Application.ActiveWorkbook.Path
MyWbName = Application.ActiveWorkbook.FullName

    ' reference a SaveAs Dialog
    Set FD = Application.FileDialog(msoFileDialogSaveAs)
    With FD
        .FilterIndex = 3

       .InitialFileName = MyWbName
       .Title = "Save As"
    End With

    FD.Show

    If FD.SelectedItems.Count = 0 Then
       Exit Sub
    Else
            ' check for proper extension
            If Right(FD.SelectedItems(1), 4) = "xlsb" Then  '= "xlsm" Then
     
                       FTyp = 50

                   Application.EnableEvents = False
                   Me.SaveAs FD.SelectedItems(1), FTyp
                   Application.EnableEvents = True

            Else
                  MsgBox "selected wrong file format ... not saving"
            End If
    End If

End Sub

[SamT]

BeforeSave Event

See Also Applies To Example Specifics

Occurs before the workbook is saved.

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)


SaveAsUi True if the Save As dialog box will be displayed.

Cancel False when the event occurs. If the event procedure sets this argument to True, the workbook isn't saved when the procedure is finished.

Example

This example prompts the user for a yes or no response before saving the workbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel as Boolean)
    a = MsgBox("Do you really want to save the workbook?", vbYesNo)
    If a = vbNo Then Cancel = True
End Sub

_BeforeSave is an EVENT. Cancel Application Events in the sub Before running the rest of the code in the sub.
Reset Events before exiting the sub.

[snb]

I'd use:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = 0
    ThisWorkbook.SaveAs Replace(ThisWorkbook.Name, ".xlsx", ".xlsb"), 50
End Sub

The introduction af an Addin seems to be more robust.

[amrane]

Dear snb

thank you sir for your help, but no restrict to maintain a given file type
I think the best way is to make condition as below:
based on the "Save as Type" file type, the Save button will be enabled or not activated

please can you tell how to do it?

thank you in advance for your support,
Amrane