VBA to Save As Macro Enabled Workbook

[ABabeNChrist]

It is possible to have code attached to a command button that when selected would then Save As existing Workbook as an Macro Enabled Workbook and also an option to name this saved Workbook and where to save using a dialog or ?.

I recorded a macro that does work, but it will not allow me the option to name Workbook first

Code: Select all

    ActiveWorkbook.SaveAs Filename:= _
     "C:\Documents and Settings\Book1.xlsm", FileFormat _
     :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

And this one will Save as Macro Enabled Workbook

Code: Select all

ActiveWorkbook.SaveAs "C:\ron.xlsm", fileformat:=52

[HansV]

You can use

Application.Dialogs(xlDialogSaveAs).Show arg2:=xlOpenXMLWorkbookMacroEnabled

[ABabeNChrist]

It works great
Thank you hansV

[ABabeNChrist]

Hi HansV
I have another question regarding Save
My workbook is a Macro-Enabled Template (xltm) and when it is opened the user would then select start new report. I used the code that you provide

Code: Select all

Application.Dialogs(xlDialogSaveAs).Show arg2:=xlOpenXMLWorkbookMacroEnabled

I use this code to start a new report as a Macro-Enabled Workbook (xlsm). This all works great
But I also have this code that allows user to save changes to xlsm, as they work on the report.
But I noticed that if the command button that runs this code were to be pressed before it was saved to a xlsm, and was still a xltm, I could developed an error if I tried to cancel out. Is there any addition code I could add that possibly stop this if this was to occur.
I hope I explained it OK

Code: Select all

Dim Answer As String
Dim MyNote As String

    'Place your opening text here
    MyNote = "You are now closing, You have the option to save changes by selecting YES or select NO to close only !"

    'Display MessageBox
    Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "???")

    If Answer = vbNo Then
        'Code for No button Press
        MsgBox "Report has not be saved - Close only!"
    ActiveWorkbook.Close savechanges:=False
    ThisWorkbook.Close

    Else
        'Code for Yes button Press
        MsgBox "Report has now been saved!"
    If ThisWorkbook.Saved = False Then
        ThisWorkbook.Save
        ThisWorkbook.Close

End If
End If

[HansV]

I don't understand the relationship between the code you were asking about higher up in this thread and the code in your last reply.

And why do you refer to both ActiveWorkbook and ThisWorkbook in the code?

BTW it would help if you indented your code consistently.

[ABabeNChrist]

Thank you HansV
I found that by using this code at the beginning

Code: Select all

Application.Dialogs(xlDialogSaveAs).Show arg2:=xlOpenXMLWorkbookMacroEnabled

when a new report is about to start, it would help in preserving the original and make it less confusing to the user about how and when to name and save a report. This is completed at the very first step.
What I noticed was the way the code seems to work, when I select the “Start new report” command button that then runs

Code: Select all

Application.Dialogs(xlDialogSaveAs).Show arg2:=xlOpenXMLWorkbookMacroEnabled

It then opens the Save as dialog I would then name the workbook and select location and then save, from there it seems to close the original and leave the save as copy opened in same location.

And now referring to the other code

Code: Select all

Dim Answer As String
Dim MyNote As String

    'Place your opening text here
    MyNote = "You are now closing, You have the option to save changes by selecting YES or select NO to close only !"

    'Display MessageBox
    Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "???")

    If Answer = vbNo Then
        'Code for No button Press
        MsgBox "Report has not be saved - Close only!"
    ThisWorkbook.Close savechanges:=False     ‘I changed Active to This on this line
    ThisWorkbook.Close

    Else
        'Code for Yes button Press
        MsgBox "Report has now been saved!"
    If ThisWorkbook.Saved = False Then
        ThisWorkbook.Save
        ThisWorkbook.Close

End If
End If 

With a command button labeled "Close" this code is used to save the workbook as the user goes along, starting and stopping or for whatever reason. Since it is now a xlsm and not a xltm it came now be saved.
The issue or concern I was referring to was when the original / Master xlsm workbook is first open, and just for whatever reason the user were to close and try to save using this code and then opt out an error message would then arise. I was hoping to be able to add something to this code that if error would arrise, would stop code.

[HansV]

How does the user "start" a report?
- By opening the template (.xltm) from Windows Explorer, or
- By clicking Office | New in Excel and selecting the template, or
- By opening the template from within Excel?

[ABabeNChrist]

Hi HansV

How does the user "start" a report?

The workbook opens to a UserForm, That I call the Main control panel.
Here is where user would start new report and or navigate around.

Main Control Panel.JPG

[HansV]

So how does the user get there?
- By opening the template (.xltm) from Windows Explorer, or
- By clicking Office | New in Excel and selecting the template, or
- By opening the template from within Excel?

[ABabeNChrist]

I guess its the Windows Explorer, I use a short cut icon on my desktop that goes to the .xltm workbook that is located in My Documents
I'm sorry, I'm still learning, I've had a 6 month crash couse - hand on learning this, before that I was pounding nail in construction 25 years

Updated: Correction 9 months - Time is sure flying

[HansV]

OK, thanks.

When you activate the shortcut, you're in fact creating a new workbook based on the template, not opening the template itself.

What is the error message that you get if you cancel the dialog shown by the Close button?

[ABabeNChrist]

Hi HansV
If i select the Close/Exit before a starting new report and then select "Yes" I then would recieve this message.

First Message.JPG

Then selectiong "No" to get out I would then recieve error message.

1004.JPG

Here is the line of code that shows error

Code error.JPG

[HansV]

Try replacing the line

ThisWorkbook.Save

with

Code: Select all

' Test whether the workbook has been saved to disk yet
If Instr(ThisWorkbook.FullName, "\") > 0 Then
  ' Yes, so we can simply save it
  ThisWorkbook.Save
Else
  ' No, so we should display the Save As dialog
  Application.Dialogs(xlDialogSaveAs).Show arg2:=xlOpenXMLWorkbookMacroEnabled
End If

[ABabeNChrist]

Hi HansV
I made the changes and ran the code; I noticed it would open the save as option dialog again.
I thought that might be a little too confusing. Because when the Workbook is Saved as a Macro-Enabled Workbook, But further review I noticed that this is not the case, when the Close/Exit button is selected again doesnt reopen the save as dialog again . I guess the little piece of code prevents that.

Code: Select all

If Instr(ThisWorkbook.FullName, "\") > 0 Then

[HansV]

If the workbook has never been saved yet, its full name will not include a path, so it will not contain a backslash \. Hence the Save As dialog will be displayed - when the workbook is saved for the first time, the user must provide a name for the workbook.

If the workbook has been saved to disk at least once, its full name will include a path, so it will contain a backslash. So the workbook will be saved without displaying the Save As dialog.

Instead of

If Instr(ThisWorkbook.FullName, "\") > 0 Then

you could also use

If ThisWorkbook.Path <> "" Then

This will have the same effect.

[ABabeNChrist]

Hi HansV
I was trying to update my last comment, I wasnt quick enought, because I thought about what about after it is saved, so I then ran through save a workbook and then reopened new workbook to find it just as you mentioned. No more Save as dialog. and no more error
Many thanksssssssss

[ABabeNChrist]

Hi HansV
This code works great, except I noticed that when a new workbook is saved. It closes the original and leaves open the new workbook open, this parts great, but I noticed it did not load my toolbar. I have to close then reopen workbook to have toolbar operational

Empty Toolbar.JPG

[HansV]

Do you explicitly close a workbook in your code? The SaveAs dialog doesn't do that.

[ABabeNChrist]

Do you explicitly close a workbook in your code? The SaveAs dialog doesn't do that.

Hi HansV
No I do not have ThisWorkBook.Close
I thought about it, but I like the way it stays open on the new report and closes the original. everything else seems to work good it just seems that this ribbon I created has to be closed and the reopened to load correctly
I also was trying different things last night trying to get it to work, no such luck
I also tried
ThisWorkBook.Refreshall
ThisWorkBook.Refreshall
ThisWorkBook.Refreshall
I think thats how it looked
I then thought about adding the code that would place a shortcut icon on my decktop. I was able to add it to the exiting code and function OK I even added code to then close workbook. I mean this method will work, but I was hoping to be able to keep workbook open. I understand a workbook cannot be closed and reopened in the same line of code.
Heres what I was able to come up with last night. I'm sure I got some BOO BOOS in there.

Code: Select all

MsgBox ("You are now starting a New Report”)
If Application.Dialogs(xlDialogSaveAs).Show(arg2:=xlOpenXMLWorkbookMacroEnabled) Then
  Dim WSHShell As Object
  Dim strDesktopPath As String
  Set WSHShell = CreateObject("WScript.Shell")
  ' Read desktop path using WshSpecialFolders object
  strDesktopPath = WSHShell.SpecialFolders("Desktop")
  If Not Right(strDesktopPath, 1) = "\" Then
    strDesktopPath = strDesktopPath & "\"
    End If
  With WSHShell.CreateShortcut(strDesktopPath & "\" & ActiveWorkbook.Name & ".lnk")
    ' Set shortcut object properties and save it
    .TargetPath = ActiveWorkbook.FullName
    .Save
  End With
  Set WSHShell = Nothing

    MsgBox ("A shortcut icon for this new report has been added to your desktop")
    MsgBox ("You may now select OPEN REPORT and begin working on your report")
    
    ActiveWorkbook.Close
    
Else
    MsgBox ("You have selected cancel")
End If

I added 2 sererate messages, it seems to work

[HansV]

I'm afraid I have no idea why you have to close and reopen the workbook. What happens if you call the code that creates the ribbon after saving the workbook?