Save As on open

[rjplante]

I have an Excel 2003 compatible spreadsheet template file. I want to have a dialog box open as the workbook opens for the first time forcing the user to save the file as an .xls or .xlsm format. This would preserve the template from tampering. I have a cell that gets changed so that when the saved file is opened it will not launch a save as dialogue every time.

I want to force the user to save the file. To that end I have a msgbox that reiterates the save request should they click cancel. The problem I have is that instead of reopening the save as dialog box when they click OK on my message box, it opens a small box that says
"A file name "False" already exists in this location. Do you want to replace it?" (YES, NO, CANCEL).

If I click cancel in this box my macro crashes with a run time error 1004, and the message "Method 'Save As' of object '_workbook' failed."

I would like to have the user forced to enter a name and not be allowed to cancel out of the dialog box. I want the template file preserved without changes so that it is always the same each time they open it.

Thanks for the help.

Robert

Private Sub Workbook_Open()

Dim fName As String

If Range("CA1").Value <> "" Then
    Exit Sub

Else

    fName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")

    If fName = False Then
        MsgBox "Please enter a file name and select a location.", vbOKOnly
        Cancel = True
        
        Application.EnableEvents = False
    
      '**** code crashes here on this next line *****
        ThisWorkbook.SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

        Application.EnableEvents = True
        
        Range("CA1").Value = "Saved"
        
    End If

    Application.EnableEvents = False
    
    ThisWorkbook.SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    Range("CA1").Value = "Saved"
    
    Application.EnableEvents = True

End If

End Sub

 

[bensonsearch]

I think the error is in the msgbox cancel button, it is returning false and trying to save it as that.

this is from the help file, maybe change according

<CODE>Dim Msg, Style, Title, Help, Ctxt, Response, MyString</CODE>
<CODE>Msg = "Do you want to continue ?"    ' Define message.</CODE>
<CODE>Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.</CODE>
<CODE>Title = "MsgBox Demonstration"    ' Define title.</CODE>
<CODE>Help = "DEMO.HLP"    ' Define Help file.</CODE>
<CODE>Ctxt = 1000    ' Define topic        </CODE>
<CODE>' context.         </CODE>
<CODE>' Display message.</CODE>
<CODE>Response = [B]MsgBox([/B]Msg[B],[/B] Style[B],[/B] Title[B],[/B] Help[B],[/B] Ctxt[B])[/B]</CODE>
<CODE>If Response = vbYes Then    ' User chose Yes.    </CODE>
<CODE>MyString = "Yes"    ' Perform some action.</CODE>
<CODE>Else    ' User chose No.    </CODE>
<CODE>MyString = "No"    ' Perform some action.</CODE>
<CODE>End If</CODE>
</PRE>