?
Solved

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

Posted on 2012-09-15
10
Medium Priority
?
3,744 Views
Last Modified: 2012-09-29
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
0
Comment
Question by:MCSignIT
  • 4
  • 4
8 Comments
 
LVL 11

Accepted Solution

by:
Swapnil Nirmal earned 2000 total points
ID: 38402981
Add below code to your template

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
Application.EnableEvents = True
End If
End Sub

Open in new window

0
 

Author Comment

by:MCSignIT
ID: 38403410
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
0
 
LVL 11

Expert Comment

by:Swapnil Nirmal
ID: 38403634
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:MCSignIT
ID: 38405137
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.
0
 
LVL 11

Assisted Solution

by:Swapnil Nirmal
Swapnil Nirmal earned 2000 total points
ID: 38406575
Which office version u use?

Try this:

    Me.SaveAs Filename:="StrSaveAs" & ".xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
0
 

Assisted Solution

by:MCSignIT
MCSignIT earned 0 total points
ID: 38407473
2010

That worked, thanks.

I am positive that I already tried xlOpenXMLWorkbookMacroEnabled rather than Format 52, but I must have had my syntax wrong.

I dropped your quotes around StrSaveAs, and also dropped the "& .xlsm" because that resulted in a file called filename.xlsm.xlsm.
0
 

Author Comment

by:MCSignIT
ID: 38412673
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.
0
 
LVL 11

Expert Comment

by:Swapnil Nirmal
ID: 38412674
I have provided the solution as commented by the author. I should be rewarded instead of closing thread.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question