neoseo1300
New Member
Hi,
I'm trying to force users to save my spreadsheet in binary xlsb. I've looked online and followed the methodology as per:
So I've added the following code in ThisWorkbook:
Although the code works as intended in most cases, it returns a run-time error 1004 in certain situations such as when the window "A file named xxxx already exists in this location. Do you want to replace it?" pops up and you click on either "No" or "Cancel" or press escape.
In this case, when I then click on debug, it highlights the line:
ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlExcel12
Do you know how to modify the code to prevent this type of error?
Also, in the page where I copied the code from (link above), certain comments indicate that it does not work on mac (can't confirm, I don't have a mac). Is it true? Would there be a fix for that too?
Thanks
I'm trying to force users to save my spreadsheet in binary xlsb. I've looked online and followed the methodology as per:
How to force users to save as a macro enabled workbook?
ExtendOffice provides a comprehensive range of professional software solutions, enhancing productivity and efficiency for businesses worldwide.
www.extendoffice.com
So I've added the following code in ThisWorkbook:
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim xFileName As String
If SaveAsUI <> False Then
Cancel = True
xFileName = Application.GetSaveAsFilename(, "Excel Binary Workbook (*.xlsb), *.xlsb", , "Save As xlsb file")
If xFileName <> "False" Then
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlExcel12
Application.EnableEvents = True
Else
MsgBox "Action Cancelled"
Cancel = True
Exit Sub
End If
End If
End Sub
Although the code works as intended in most cases, it returns a run-time error 1004 in certain situations such as when the window "A file named xxxx already exists in this location. Do you want to replace it?" pops up and you click on either "No" or "Cancel" or press escape.
In this case, when I then click on debug, it highlights the line:
ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlExcel12
Do you know how to modify the code to prevent this type of error?
Also, in the page where I copied the code from (link above), certain comments indicate that it does not work on mac (can't confirm, I don't have a mac). Is it true? Would there be a fix for that too?
Thanks