0

While manually saving an Excel Addin file as xla I'm unable to suppress an Excel eror message. The error measage occurs before the Workbook_BeforeSave event is triggered.
In Workbook_BeforeSave event I am setting the IsAddin back to TRUE, and the file is saved, just want to block the error notification that makes the user have to press OK and then realize that the error was not a real problem. If the Workbook_BeforeSave is not really exactly before the save what other event happens earlier that I can manipulate with vba? How could I hide the unnessecery measage from the user and let the user just press Excels regular save button?

 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'MsgBox "Workbook_BeforeSave triggered"

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

If Not ThisWorkbook.IsAddin Then
        ThisWorkbook.IsAddin = True
End If
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

Private Sub showSheetsOfAddin()
If ThisWorkbook.IsAddin Then
 ThisWorkbook.IsAddin = False
End If
End Sub
Noam Brand
  • 335
  • 3
  • 13
  • 1
    Can you give details of that message? What is the content? When exactly does it appear? How can you tell that it appears *before* the `Workbook_BeforeSave` event is triggered? – FunThomas Sep 22 '22 at 13:22
  • Theoretically, no other event is triggered but anyhow, as stated above, you should understand what raising that **specific** error. Before saving, the compiler checks if the code has something unable to be compiled, procedures having the same name, API functions not adapted to 64/32 Bit and so on. A good idea is to proceed as recommended above. If you really need help, please write in clear what error is raised and which code line is highlighted (or not...). Otherwise, I am afraid that you cannot receive any help... – FaneDuru Sep 22 '22 at 13:48
  • The file can not be saved, A file is in a different format than its extension indicates. It is an Excel message error (not a syntax or compile error). – Noam Brand Sep 22 '22 at 14:32
  • It appears before the Workbook_BeforeSave event is triggered because the MsgBox "Workbook_BeforeSave triggered" appears after I press OK on the error measage. – Noam Brand Sep 22 '22 at 14:39
  • Should we understand that "The file can not be saved, A file is in a different format than its extension indicates" is **exactly** the error message? Did you previously use `ThisWorkbook.IsAddin = False`? Theoretically, you cannot "manually" save an add-in, since it is not visible. If this is the case, why don't you save it in code, using `ThisWorkbook.IsAddin = False` as first code line? Even, creating such a `Save` button on the add-in interface. If not, please better explain all the context. – FaneDuru Sep 22 '22 at 18:18
  • Yes and yes. The user might anyway press Excels save button .Creating an additional save button that makes isaddin true is possible but less user friendly because I will have to tell the user "please remember to press some button before saving the file or you will get an error measage, but if you forgat you may ignore the error measage", I was hoping to use Excels original save button. – Noam Brand Sep 22 '22 at 19:58
  • Maybe should have used Application.WorkbookBeforeSave instead of Workbook.BeforeSave? https://powerspreadsheets.com/excel-vba-events/#ApplicationWorkbookBeforeSave-And-WorkbookBeforeSave – Noam Brand Sep 22 '22 at 23:16
  • Can you explain **why is it necessary to use `ThisWorkbook.IsAddin = False`**? Does the add-in store some new information in its sheets and needs to be saved because of that? If so, I suppose that the new information is placed there by code and in such a case that code should be ending with `ThisWorkbook.Save`... Am I missing something? Even if the user **needs to see that sheet, you may add a `Validate` button to be pressed, instead of manually saving**, which to use `IsAddin = False` and save it, of course... – FaneDuru Sep 23 '22 at 06:23
  • It is necessary for showing a help/demo sheet for explanation about how to use the addin. – Noam Brand Sep 23 '22 at 15:44
  • This can also be used for letting the user save sheets even if it is an addin that will be available whenever excel is opened (withot using other methods that destroy the users personal xlsb) – Noam Brand Sep 23 '22 at 15:51
  • The exact msg is: "This extension cannot be used with the selected file type. Change the file extension in the File name text box or choose a different file type by changing the type in the Save as box." – Noam Brand Sep 28 '22 at 11:30

0 Answers0