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").Rang e("C1").Va lue = 0 Then
ActiveWorkbook.SaveAs Filename:="Quote Form", FileFormat:=xlOpenXMLWorkb ookMacroEn abled
Exit Sub
End If
If Sheets("CheckBlanks").Rang e("A1").Va lue = True Then
Msg = Msg + Chr(10) + "Bid Per (Value Engineer or Drawing)"
End If
If Sheets("CheckBlanks").Rang e("A2").Va lue = True Then
Msg = Msg + Chr(10) + "Install Budget Needed (Yes or No)"
End If
If Sheets("CheckBlanks").Rang e("A3").Va lue = True Then
Msg = Msg + Chr(10) + "Description (list signs in 'Description' column)"
End If
If Sheets("CheckBlanks").Rang e("A4").Va lue = True Then
Msg = Msg + Chr(10) + "City"
End If
If Sheets("CheckBlanks").Rang e("A5").Va lue = True Then
Msg = Msg + Chr(10) + "State"
End If
If Sheets("CheckBlanks").Rang e("A6").Va lue = True Then
Msg = Msg + Chr(10) + "Requester (your name)"
End If
If Sheets("CheckBlanks").Rang e("A7").Va lue = True Then
Msg = Msg + Chr(10) + "Customer name"
End If
If Sheets("CheckBlanks").Rang e("A8").Va lue = True Then
Msg = Msg + Chr(10) + "Estimated Project Completion Date"
End If
If Sheets("CheckBlanks").Rang e("A9").Va lue = True Then
Msg = Msg + Chr(10) + "Ship Date"
End If
If Sheets("CheckBlanks").Rang e("A10").V alue = 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
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").Rang
ActiveWorkbook.SaveAs Filename:="Quote Form", FileFormat:=xlOpenXMLWorkb
Exit Sub
End If
If Sheets("CheckBlanks").Rang
Msg = Msg + Chr(10) + "Bid Per (Value Engineer or Drawing)"
End If
If Sheets("CheckBlanks").Rang
Msg = Msg + Chr(10) + "Install Budget Needed (Yes or No)"
End If
If Sheets("CheckBlanks").Rang
Msg = Msg + Chr(10) + "Description (list signs in 'Description' column)"
End If
If Sheets("CheckBlanks").Rang
Msg = Msg + Chr(10) + "City"
End If
If Sheets("CheckBlanks").Rang
Msg = Msg + Chr(10) + "State"
End If
If Sheets("CheckBlanks").Rang
Msg = Msg + Chr(10) + "Requester (your name)"
End If
If Sheets("CheckBlanks").Rang
Msg = Msg + Chr(10) + "Customer name"
End If
If Sheets("CheckBlanks").Rang
Msg = Msg + Chr(10) + "Estimated Project Completion Date"
End If
If Sheets("CheckBlanks").Rang
Msg = Msg + Chr(10) + "Ship Date"
End If
If Sheets("CheckBlanks").Rang
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.GetSaveAsFilen ame _
(FileFilter:="Excel Macro-Enabled Workbook(*.xlsm) *.xlsm")
Me.SaveAs strSaveAs, fileformat:=52
Application.EnableEvents = True
End If
End Sub
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.GetSaveAsFilen
(FileFilter:="Excel Macro-Enabled Workbook(*.xlsm) *.xlsm")
Me.SaveAs strSaveAs, fileformat:=52
Application.EnableEvents = True
End If
End Sub
ASKER
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.
"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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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").Rang
Cancel = True
Application.EnableEvents = False
strSaveAs = Application.GetSaveAsFilen
(FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
Me.SaveAs strSaveAs
Application.EnableEvents = True
End If
If Sheets("CheckBlanks").Rang
If Sheets("CheckBlanks").Rang
If Sheets("CheckBlanks").Rang
If Sheets("CheckBlanks").Rang
If Sheets("CheckBlanks").Rang
If Sheets("CheckBlanks").Rang
If Sheets("CheckBlanks").Rang
If Sheets("CheckBlanks").Rang
If Sheets("CheckBlanks").Rang
If Sheets("CheckBlanks").Rang
If Sheets("CheckBlanks").Rang
MsgBox Msg, , "File Not Saved"
Cancel = True
End If
End Sub