remove all save options from just one workbook

[2funny4words]

I have a save button running a macro that validates before saving. Is there a way to remove all other save options (alt f a, ctrl s, the toolbar save button, etc.) but just from this one workbook?

A while ago I tried playing with a code that did this but it did it through out excel. not cool

I assume that I need a case statement that says something like

case.sheet="filename"
       remove save options
case else
end

I really don't know vb just playing with a couple of reports for our local volunteer fire department trying to simplify things.

Thanks for your help

[karan]

Hi,

You can restrict the save option by putting a couple of lines of code in the 'ThisWorkbook' module in Visual Basic Editor.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Sheets(1).cells(1,1).value = "2funny4words" Then
    Cancel = False
Else
    Cancel = True
End If

End Sub

You need to give at least one option of saving so that you can save the workbook after you write the code or else the code could never be incorporated.

In the above code, I have mentioned the condition as: Cell A1 in the first sheet having text as '2funny4words'. Once you are done with writing the code, enter text as indicated above and press Ctrl + S to make a final save. Then, delete that text and you can prevent the workbook from being saved unless someone can see and interpret the macro code, which of course you can password protect.

--Karan--

[Phil_V]

In the above code, I have mentioned the condition as: Cell A1 in the first sheet having text as '2funny4words'. Once you are done with writing the code, enter text as indicated above and press Ctrl + S to make a final save. Then, delete that text and you can prevent the workbook from being saved unless someone can see and interpret the macro code, which of course you can password protect.

Surely that will cause a problem?
So you type your code, enter the test "2funny4words" into cell A1 and great, you can save your workbook.
However so can the final user, unless of course you delete the text "2funny4words", so you do that; you open the workbook, delete the text from the cell.... but now what? You can't save the workbook, (that would require the text there), and if you close it without saving, then next time you open the workbook the text will be back

If you are going to use a keytext like this to allow saving, you will need the macro to remove it BEFORE it does the save:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

' Test for the 'keytext' to see if saving is allowed
If Sheets(1).cells(1,1).value = "2funny4words" Then
    ' Text present, saving is allowed
    Sheets(1).cells(1,1).value=""  ' Delete the keytext
    Cancel = False  ' Allow the save to take place
Else
    ' Text not present, saving is not allowed
    Cancel = True  ' Stop the save from taking place
End If

End Sub

Of course if you find you need to edit the workbook again for some reason, just enter that keytext "2funny4words" into cell A1 and you will be able to save the workbook once.

[karan]

Hey Phil,

You got that right!!! I had not considered the saving after deletion of that value from cell A1.

Regards,
Karan

[2funny4words]

Thanks for your input. I copy and pasted the code and added a message box and for some reason I can still save the file even if cell a1 is blank.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Sheets(1).Cells(1, 1).Value = "2funny4words" Then
    Sheets(1).Cells(1, 1).Value = ""
    Cancel = False
    MsgBox "Please use the save button at the end of the report-Save Cancelled"
     
Else
    Cancel = True
End If

End Sub

Also when I get the bugs worked out, my cmdsave button will still work right? It saves a copy of the report as a different file based on data in a specific cell.

[Phil_V]

You have your code the wrong way around, the top option is for when you ARE allowed to save the code. It should be like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Cells(1, 1).Value = "save" Then
    Cells(1, 1).Value = ""
    Cancel = False
Else
    Cancel = True
    MsgBox "Save not allowed"
End If
End Sub

Make sure it is in the 'ThisWorkbook' part of the VBA editor too.