Link to home
Start Free TrialLog in
Avatar of MCSignIT
MCSignIT

asked on

Excel Macro-enabled template - default save-as type to *.XLSM

All-

I have a form where I'm preventing users from saving the file unless they fill in certain fields.  This form is saved as a macro-enabled template.  The problem is that when the user opens, fills in, and saves the form, it defaults to *.XLSX, and all my code gets stripped out.  I need to default that to *.XLSM.  I have found a few suggestions online for how to do this; none of them work.

The probem with my code below is that it it does one of two things:
1) Save a file called "Quote Form.xlsm" to the users My Documents
2) Open a prompt saying the user already has a file by that name in that directory
Instead, I want the Save As dialogue to open.

Thanks for your help.

*******

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim Msg As String
Msg = "Please add the following information before saving this form:" + Chr(10)

If Sheets("CheckBlanks").Range("C1").Value = 0 Then
    ActiveWorkbook.SaveAs Filename:="Quote Form", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    Exit Sub
End If

If Sheets("CheckBlanks").Range("A1").Value = True Then
    Msg = Msg + Chr(10) + "Bid Per (Value Engineer or Drawing)"
End If

If Sheets("CheckBlanks").Range("A2").Value = True Then
    Msg = Msg + Chr(10) + "Install Budget Needed (Yes or No)"
End If

If Sheets("CheckBlanks").Range("A3").Value = True Then
    Msg = Msg + Chr(10) + "Description (list signs in 'Description' column)"
End If

If Sheets("CheckBlanks").Range("A4").Value = True Then
    Msg = Msg + Chr(10) + "City"
End If

If Sheets("CheckBlanks").Range("A5").Value = True Then
    Msg = Msg + Chr(10) + "State"
End If

If Sheets("CheckBlanks").Range("A6").Value = True Then
    Msg = Msg + Chr(10) + "Requester (your name)"
End If

If Sheets("CheckBlanks").Range("A7").Value = True Then
    Msg = Msg + Chr(10) + "Customer name"
End If

If Sheets("CheckBlanks").Range("A8").Value = True Then
    Msg = Msg + Chr(10) + "Estimated Project Completion Date"
End If

If Sheets("CheckBlanks").Range("A9").Value = True Then
    Msg = Msg + Chr(10) + "Ship Date"
End If

If Sheets("CheckBlanks").Range("A10").Value = True Then
    Msg = Msg + Chr(10) + "Quote Mfg / Quote Install (use an 'X' to indicate which items you need quoted)"
End If

MsgBox Msg, , "File Not Saved"

Cancel = True

End Sub
ASKER CERTIFIED SOLUTION
Avatar of Swapnil Nirmal
Swapnil Nirmal
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MCSignIT
MCSignIT

ASKER

My current code is below.  I added the missing a comma and space in the FileFilter line.

Closer, but still not working.  I had tried this before with no luck, but got it to work halfway this time.  It opens the Save As dialogue box.  "Save As Type" shows Macro-Enabled Workbook, and that's the only option in the dropdown.  When I click SAVE, though, I get this error:

"The following features cannot be saved in macro-free workbooks:
-VB project
To save a file with these features, click No, and then choose a macro-enabled file in the File Type list.
To continue saving as a macro-free workbook, click Yes."

So I've gotten the UI to display the macro option, but it seems like I'm missing a step to actually declare the FileFormat.  I thought about appending ".xlsm" to the displayed file name, but if the user renames the file before saving, I can't count on them to re-type the extension.  

What am I missing?  



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim Msg As String, strSaveAs As String

Msg = "Please add the following information before saving this form:" + Chr(10)

If Sheets("CheckBlanks").Range("C1").Value = 0 Then
    Cancel = True
    Application.EnableEvents = False
    strSaveAs = Application.GetSaveAsFilename _
    (FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
    Me.SaveAs strSaveAs
    Application.EnableEvents = True
End If

If Sheets("CheckBlanks").Range("A1").Value = True Then Msg = Msg + Chr(10) + "Bid Per (Value Engineer or Drawing)"
If Sheets("CheckBlanks").Range("A2").Value = True Then Msg = Msg + Chr(10) + "Install Budget Needed (Yes or No)"
If Sheets("CheckBlanks").Range("A3").Value = True Then Msg = Msg + Chr(10) + "Description (list signs in 'Description' column)"
If Sheets("CheckBlanks").Range("A4").Value = True Then Msg = Msg + Chr(10) + "City"
If Sheets("CheckBlanks").Range("A5").Value = True Then Msg = Msg + Chr(10) + "State"
If Sheets("CheckBlanks").Range("A6").Value = True Then Msg = Msg + Chr(10) + "Requester (your name)"
If Sheets("CheckBlanks").Range("A7").Value = True Then Msg = Msg + Chr(10) + "Customer name"
If Sheets("CheckBlanks").Range("A8").Value = True Then Msg = Msg + Chr(10) + "Estimated Project Completion Date"
If Sheets("CheckBlanks").Range("A9").Value = True Then Msg = Msg + Chr(10) + "Ship Date"
If Sheets("CheckBlanks").Range("A10").Value = True Then Msg = Msg + Chr(10) + "Quote Mfg / Quote Install (use an 'X' to indicate which items you need quoted)"

If Sheets("CheckBlanks").Range("C1").Value > 0 Then
    MsgBox Msg, , "File Not Saved"
    Cancel = True
End If

End Sub
Try This:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim strSaveAs As String
If SaveAsUI = True And Me.FileFormat = xlTemplate Then
Cancel = True
Application.EnableEvents = False
'Not sure of FileFilter
strSaveAs = Application.GetSaveAsFilename _
(FileFilter:="Excel Macro-Enabled Workbook(*.xlsm) *.xlsm")
Me.SaveAs strSaveAs, fileformat:=52
Application.EnableEvents = True
End If
End Sub
Odd... with that addition, when I save the file, I get the Compatibility Checker warning, warning me that I am going to lose functionality if I save in this format.  (It's as if I was saving it as *.xls.)  I proceeded with the save anyway.  It created a *.xlsm file, however, I was unable to open the file; I got the error,
"Excel cannot open the file '<filename>' because the file format or file extension is not valid.  Verify that the file has not been corrupted and that the file extension matches the format of the file."  
I renamed it *.xls and it opened fine.

Needless to say, if I put Excel in Design mode and Save As / Macro Enabled Workbook, I don't get that warning.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I've requested that this question be closed as follows:

Accepted answer: 0 points for MCSignIT's comment #a38407473
Assisted answer: 250 points for n_swapnil's comment #a38402981
Assisted answer: 250 points for n_swapnil's comment #a38406575

for the following reason:

Successfully forced users of a macro-enabled template to save as a macro-enabled spreadsheet.
I have provided the solution as commented by the author. I should be rewarded instead of closing thread.