disable save/save as if condition is not met

[thoandros]

Hello.
It's the whole day I am trying to find across the web an easy solution but all codes and answers I see for similar cases are always not 100% what I was looking for.

Hopefully someone is able to help me or redirect to a solution that fits my problem 100%.



My file is like a form the clients have to fill and many cells/fields are mandatory. I have already created a formula that once they have filled all mandatory fields, cell C1 will show "FORM OK".

Problem:
What I need is - I think - rather easy: unless cell C1 reads "Form OK" they should not able to save the file. So it's either they close without saving, or fill all the parts and then save.
(FYI it's a read only file, protected sheets and workbook and a password also to access vba code)



Can you help me to create a code or find a solution?

thank you very much in advance to anyone that can help

[Mumps1]

Place this macro in the code module for ThisWorbook:

Please Login or Register  to view this content.

[thoandros]

thank you, it works however I have a chicken and egg situation... how can I save it now to have it working from the next time? When I click on save - to create the final version of the blank template I would like to send to all clients - it prompts the message! Is there anything I can do that will allow me to save it at least once?

[Mumps1]

Open the VBA Editor by holding down the ALT key and pressing the F11 key. In the top menu, just below the 'Tools' item, you will see the 'Design Mode' icon. Just hold your mouse pointer over the icon and the icon name will be displayed. Click the icon so that it is highlighted. You are now in 'Design Mode' and the macros won't work. Next click the 'Save' icon to the menu to the left. The file has now been saved. Click the 'Design Mode' icon again to un-highlight it. This icon is an "on/off" switch. When it is on, macros are disabled, when it is off, macros are enabled.

[thoandros]

thank you, it worked

[Mumps1]

You are very welcome.

[thoandros]

Mumps1, another quick question.
I have added a code that before saving, if the C1 condition is met, the entire sheet is protected from editing. This because I do not want them to use the file created (which can be macro not nabled) as a template... so that they avoid doing a new form again.

So, question is.. given the C1 cell is ok.. how can I get a message pop-up to warn "are you sure you want to save the file? please note that once saved the fields cannot be amended anymore" and they can cancel and go back or select ok.. then the code to protect everything goes in and save?

thanks for your help

[Mumps1]

Try this macro. Before saving the workbook, "Sheet1" will be locked and protected so it can't be edited. Change the sheet name and password to suit your needs.

Please Login or Register  to view this content.

[thoandros]

thank you Mumps1
the code I used for protecting the sheet is this, but I think it does the same job as yours:

(the file was already protected, so I needed to unprotect, select all cells, protect again)

ActiveSheet.Unprotect Password:="mypassword"
ActiveWindow.DisplayHeadings = True
Cells.Select
Selection.Locked = True
Selection.FormulaHidden = True
ActiveWindow.DisplayHeadings = False
ActiveSheet.Protect Password:="mypassword", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoRestrictions



it worked.

however now, just to complicate more the things.. I would like that once they click on save (which redirects to a save as - being the file read-only), they are prompted to save a macro-free file format, with a specific filename coming from a cell.

NOw, I tried to put the below in the code but it re-triggers the save procedure etc... so I do not know where and how I can do this.. can you help?

Dim filename As String
filename = Range("F4")
ActiveWorkbook.SaveAs filename:=filename & ".xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False


Note: I know that the above code prompts the warning about saving a macro free file - that is ok for me as if I use to hide that warning, I will also prevent the user to decide into which folder to save the file, and this is something I don't want so it's ok they see that warning, and they are instructed to click on yes (unless there is a way to make select the yes in automatic, but still show the folder).

thank you so much

[Mumps1]

You can avoid having the "Saveas" pop up appear by having the macro save the file for you without any messages popping up but you can't use a Workbook_BeforeSave macro to do that because this type of event opens the dialogue window. Do you want the user to choose the folder in which to save the file and then have the macro save it with the value from F4? Could you please post the entire code you are now using?

[thoandros]

Ideal process should be:

user fills all fields
if user tries to save before FORM COMPLETED is showing in C1 he will get message he needs to complete the form as he cannot save otherwise.

once all fields are ok and "FORM COMPLETED" is showing in C1 user is able to click on save (which is acting as "Save AS" being the file read only)


user is warned that once saved, the file cannot be amended anymore (because the macro locks all cells and puts the password)

if user clicks cancel, goes back, if clicks OK the file is then saved as macro free, with the name as suggested in cell F4. I would like that the user is able to select into which folder to save.

hope this is sufficient and thank you so much in advance.




here the current code:



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Range("C1") <> "FORM COMPLETED" Then
MsgBox "Saving of this file is not permitted until all fields are completed. Ensure you see FORM OK in the upper bar."
SaveAUI = False
Cancel = True

Exit Sub
Else
If MsgBox("Are you sure you want to save the file?" & Chr(10) & "Please note that once saved, the fields cannot be amended anymore.", vbYesNo) = vbNo Then
SaveAUI = False
Cancel = True
Exit Sub
End If
End If

ActiveSheet.Unprotect Password:="MyPassword"
ActiveWindow.DisplayHeadings = True
Cells.Select
Selection.Locked = True
Selection.FormulaHidden = True
ActiveWindow.DisplayHeadings = False
ActiveSheet.Protect Password:="MyPassword", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoRestrictions

End Sub

[Mumps1]

I was mistaken when I said that you can't use a Workbook_BeforeSave macro. Try this in the code module for ThisWorkbook:

Please Login or Register  to view this content.

[Scorpvin]

How would you change this so instead of "C10" for the range, it would be "C10:C20"?

[Mumps1]

This directly answers your question but I'm not sure if this is exactly what you are looking for.

Please Login or Register  to view this content.