Excel. Two questions, Save as, enable macros

[gustaf111]

Hello all! I have two questions ...

1. I would like to force the user to input a name when the excel document is saved, i.e to disable the save button and only allow the Save as, is that possible ?

2. Is it possible from vba to enable marcos when a excel document is opened ? or check if marcros are enabled and pop-up a warning if the marcros are not enabled ?

Thanks!
Gustaf

 

[MakeItSo]

Hi Gustaf

1.) Easiest way to accomplish this is by creating a template, i.e. an XLT. This way, user automatically must assign a name when saving.

2.)
a) How do you suppose a "checking" macro could run if mcros were disabled?
b) What would be the point in a "macros diabled security setting if said setting could be disabled by a macro?

==>Nope, not possible.

Cheers,
MakeItSo

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[gustaf111]

Hello!

Thanks!

You are correct abot the marcos ! I will hava a sheet saying "please enable macros" if the marcos is not enabled Otherwise I switch to the template with macros

About the save as, I would like the user to not over write an existing document my misstake. I got an template, but when the user save a version of that document, when could by misstake overwrite there document!

 

[MakeItSo]

That's what I meant. You can set up the template just like any workbook, with text on it and all.
However, if the user opens that template - via double click! - then a new document is created as a copy of that template. it won't get ovewritten and its inital name will be "Book1".


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[gustaf111]

But the user can start from an existing document to make a new document and by mistake save the new document over the old document So I want to prevent/disable the Save Excel option ...

 

[gustaf111]

I found what I was looking for:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
Cancel = True
MsgBox "You cannot save this workbook. Use Save As"
End If
End Sub

Sorry for taking your time!

 

[gustaf111]

But how to open the save as dialog instead... ?

 

[gustaf111]

I have the code as below, but how to catch if cancel is clicked in the SaveAs pop-up ?


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error GoTo Error
Dim filename As String
If SaveAsUI = False Then
fileSaveName = Application.GetSaveAsFilename(fileFilter:="xlsm Files (*.xlsm), *.xlsm")

' <------------ Wants to catch if cancel button is clicked here
If fileSaveName <> "False" Then
Application.EnableEvents = False
ActiveWorkbook.SaveAs fileSaveName, FileFormat:=52
Application.EnableEvents = True
End If
Cancel = True
End If
Exit Sub
Error:
Application.EnableEvents = True
Cancel = True
End Sub

 

[gustaf111]

I notice that it is languge dependet .... I'm using a swedisk edition so it will be Falskt (instead of "False") on my computer ... it does not return a code that you can use instead ?

 

[combo]

Use:
[tt]If fileSaveName <> False Then[/tt]

In case of your second question, it's possible if you open a workbook with macro (from master workbook):
[tt]FileOpenName = Application.GetOpenFilename
If FileOpenName<>False Then
Application.AutomationSecurity = msoAutomationSecurityLow
Workbooks.Open FileOpenName
End If[/tt]

combo

 

[gustaf111]

Thanks Combo. I have this solution that semas to work. I got an error if I press no/cancel on the question to overwrite an existing file, but I true that error away, I hope that it is oaky.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error GoTo Error
Dim fileSaveName As Variant
Dim Fs As String

If SaveAsUI = False Then
ChDir Me.Path
fileSaveName = Application.GetSaveAsFilename(fileFilter:="xlsm Files (*.xlsm), *.xlsm")
If TypeName(fileSaveName) <> "Boolean" Then
Application.EnableEvents = False
ActiveWorkbook.SaveAs fileSaveName, FileFormat:=52
Application.EnableEvents = True
End If
Cancel = True
End If
Exit Sub
Error:
Application.EnableEvents = True
Cancel = True
End Sub