VBA-Code - "On Error: hit "yes" in the error-message-box"

[offy]

Hello everybody,

I thought this forum or even Google could help me, but in fact I'm just getting wrong search results for slighty different topics.

My problem is: I have a VBA-code that copies an Excel-sheet, saves it separately and sends it via MS Outlook. I'm working with Windows 7 and Excel version 2007. The copy of the excel-sheet is to be sent to different receivers - some of them use Excel 2003, some use 2007 or higher.
However, when I want to save the copy as an .xls file for the Excel 2003 users, an error occurs telling me, that something might go wrong and whether I wanted Excel to recalculate all cells the next time the worksheet will be opened.

The code is to send 100 copies each time you hit "send", so it will give me 60 or more error messages for all excel 2003 users.

I want the code to automatically "click no" for the appearing message.

How could I implement that?
Is there something like "Error.MsgBox = vbNo"?

Thank you so much!

 

[Mohammad Basem]

Welcome to the forum!

Try On Error Resume Next statement just before the line causing the error.

Posting the code helps understanding and fixing it.

 

[offy]

I did try "On Error Resume Next" but it didn't have any effect. Maybe I placed it on the wrong line...
ok, this is - roughly - the code.

 Dim sh As Worksheet
 Dim wb As Workbook
 Dim FileExtStr As String
 Dim FileFormatNum As Long
 Dim TempFilePath As String
 Dim TempFileName As String

On Error Resume Next
MkDir ("C:\Temp")
Resume Next

TempFilePath = "C:\Temp\"
   If Val(Application.Version) < 12 Then
        'Excelversion 97 - 2003 found
        FileExtStr = ".xls": FileFormatNum = -4143
        
    Else
        'Excelversion 2007 found
        'this makes let the code operate on both: Excel 2007 or Excel 2003
        FileExtStr = ".xlsm": FileFormatNum = 52
   End If

Set sh = ThisWorkbook.Worksheets(2)
sh.Copy
Set wb = ActiveWorkbook

.... the actual code filling the empty worksheets with data ....

TempFileName = ThisWorkbook.Worksheets(3).Cells(2, 33).Value & "_" & ThisWorkbook.Worksheets(3).Cells(2, 34).Value & "_XYZ_" & sh.Name

On Error GoTo SaveAsError
wb.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum

SaveAsError:
Select Case Err.Number

 Case Is < 0
 
    Antw = MsgBox("Outlook has reached maximum storage capacity", vbOKOnly + vbCritical, "Error while saving")
    wb.Close
    Resume SetOhneLöschen

  Case Else

    Antw = MsgBox("An error has occured during saving. Close Excel and try again!", vbOKOnly + vbCritical, "Eorrir while saving")
    Resume SetOhneLöschen
    Unload Me
    Exit Sub

End Select

SetOhneLöschen:
....

 

[Mohammad Basem]

I haven’t encountered any error. Here is the file I have worked on. I have modified the code to always save as .xls.

The error handler routine is not properly handling the errors as it always shows error message because of the Case Else. Other than that, everything is just fine.

Am I missing something?

 

[offy]

Thank you very much for you help!
Unfortunately it still doesn't work.

The problem is not in the code, it's just the way to answer to the error which doesn't occur in the sheet you made.

This is the error message I get. The translation is: "The defined names or formulas for this worksheet can show different values for a recalculation into a former version of MS Excel. This might be due to one of the following reasons:
- There might be a reference to cells outside of the boundaries of rows or columns of a former excel version
- There might be formulas which are not supported by a former excel version
- There might be formulas that reach maximum interlacing (no support of more than 7 levels of interlace)

Should Excel recalculate all formulas in this workbook on opening this Sheet?"

--> Yes, No, Cancel

I don't know which formula exactly causes the problem, but I rather want to make Excel hit "no" automatically. On Error Resume Next does not work - the error will show up anyway.

 

[Jonmo1]

The problem is that it's not really an Error....It's just a Warning Message..
So On Error doesn't capture it...it's not an error.

Try

Application.DisplayAlerts = False

 

[Mohammad Basem]

Since you are sharing the sheet with others, why don't you convert all formulas to values?

 

[offy]

@jonmo:
Great! This works!!
Thank you

@Mohammad: I know what you mean and in fact the sheet itself exists of value-copies only. But the structure is so complicated that I must have missed one or two formulas. Also I don't want to program everything again...some bit of cosmetic painting does its job :D thank you nevertheless for your help!

 

[Mohammad Basem]

Glad to know the problem was solved. Al-hamdullah

Converting formulas to values is very simple as you don’t need to test every cell,

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Set Rng = ActiveSheet.UsedRange
Rng.Copy
Rng.PasteSpecial Paste:=xlPasteValues[/COLOR][/SIZE][/FONT]

 

[Jonmo1]

Glad to help, thanks for the feedback

@jonmo:
Great! This works!!
Thank you