Incorrect Function when Macro run from Add-Ins Ribbon

[SimonBailey]

Hi
I have a Macro to save a spreadsheet by a variable name as an xlsm file. The macro works fine if run via the VBA editor and if a button on the spreadsheet is linked to the macro. However I have several macro buttons that are on the Add-Ins tab. When this macro is run from the Add-Ins tab, it all works and saves the file as the correct name, but then an error box pops up which says Incorrect function, as there is no error message the other 2 ways I can only assume that it is something to do with it being in the Add-Ins ribbon. All the other macros that are in the ribbon work fine though. Anyone got any ideas.
Thanks
Simon

 

[mole999]

what is the code?

a lazy way if all works normally, might be to add

application.enablewarnings = false

 

[SimonBailey]

My Code is

Sub SaveAsName()
Dim sFileName As String
Dim Namer
   
On Error GoTo ErrorHandler
    
    Namer = Range("Tables!AD8")
   
    Sheets("Quote Form").Select
    'Show the open dialog and pass the selected file name
    'to the String variable "sFileName"
    sFileName = Application.GetSaveAsFilename(Namer, "Excel files (*.xlsm),*.xlsm")
    'They have cancelled
    If sFileName = "False" Then Exit Sub
    ActiveWorkbook.SaveAs sFileName, _
        FileFormat:=52, CreateBackup:=False
  
    End
ErrorHandler:
errmsg = MsgBox("An Error Has Occurred." & Chr(13) & "The file may not have been saved." & Chr(13) & "This may be due to a inappropriate file name. Ensure that file name does not contain usnsuitable characters, eg. [, *, / etc..", vbOKOnly, "Error")
End
End Sub

but as I say it doesn't seem like a problem with the code. Would the code you suggested go at the beginning?
Cheers
Simon

 

[mole999]

just tried this

first time through I get to here

Sub SaveAsName()
Dim sFileName As String
Dim Namer
   
On Error GoTo ErrorHandler
    
    Namer = Range("Tables!AD8")
   
    Sheets("Quote Form").Select
    'Show the open dialog and pass the selected file name
    'to the String variable "sFileName"
    sFileName = Application.GetSaveAsFilename(Namer, "Excel files (*.xlsm),*.xlsm")

and the code ends, dosent get to an error or even end sub

if i run again

Sub SaveAsName()
Dim sFileName As String
Dim Namer
   
On Error GoTo ErrorHandler
    
    Namer = Range("Tables!AD8")
   
    Sheets("Quote Form").Select
    'Show the open dialog and pass the selected file name
    'to the String variable "sFileName"
    sFileName = Application.GetSaveAsFilename(Namer, "Excel files (*.xlsm),*.xlsm")
    'They have cancelled
    If sFileName = "False" Then Exit Sub
    ActiveWorkbook.SaveAs sFileName, _
        FileFormat:=52, CreateBackup:=False
  
    End
ErrorHandler:
errmsg = MsgBox("An Error Has Occurred." & Chr(13) & "The file may not have been saved." & Chr(13) & "This may be due to a inappropriate file name. Ensure that file name does not contain usnsuitable characters, eg. [, *, / etc..", vbOKOnly, "Error")
End
End Sub

 

[SimonBailey]

I think you pointed me in the right direction, probably doesn't get to the route cause, but stops the error coming up, I inserted Application.DisplayAlerts = False after the error handler as below and it seems to have got rid of the problem. Strange it only did it from the Add-Ins ribbon button though, that's the thing that gets me.

On Error GoTo ErrorHandler
Application.DisplayAlerts = False
    Namer = Range("Tables!AD8")

 

[SimonBailey]

Actually had to change that to make it true at the beginning and a false right at the end otherwise the warning that a file name alreays exists doesn't appear.

Sub SaveAsName()
Dim sFileName As String
Dim Namer
   
On Error GoTo ErrorHandler
Application.DisplayAlerts = True
    Namer = Range("Tables!AD8")
   
    Sheets("Quote Form").Select
    'Show the open dialog and pass the selected file name
    'to the String variable "sFileName"
    sFileName = Application.GetSaveAsFilename(Namer, "Excel files (*.xlsm),*.xlsm")
    'They have cancelled
    If sFileName = "False" Then Exit Sub
    ActiveWorkbook.SaveAs sFileName, _
        FileFormat:=52, CreateBackup:=False
  Application.DisplayAlerts = False
    End
ErrorHandler:
errmsg = MsgBox("An Error Has Occurred." & Chr(13) & "The file may not have been saved." & Chr(13) & "This may be due to a inappropriate file name. Ensure that file name does not contain usnsuitable characters, eg. [, *, / etc..", vbOKOnly, "Error")
End
End Sub

Cheers for the help.

 

[mole999]

a test with CASE statements might help, or IF THEN ELSE

have a look at http://www.mztools.com/index.aspx might be some help in there constructing them.