I'm back again.

I can't quite find the solution to this issue.

I am using saveasUI to disable the save option, but I do have a scenerio where I would like the user to be able to save.

It is all locked down in the code, so the user basically doesn't have to do anything different.

I have a trigger cell which will input the value 1 when I want it to save. The code removes all values except what I want it to keep, then should save down, but since turning on saveasUI, it fails.

Is there a way I can code it to say, turn off saveasUI, run this code, turn on saveasUI?

Many thanks
Kempes

Fixed it.

Simply added value 2 as the flag to lift the save barring.

Works a treat

What do you mean you are using "SaveAsUI"? AFAIK SaveAsUI is a parameter of the before save event. As SaveAsUI is a boolean value it can either be true of false. So if SaveAsUI is really the issue here then set it to true then set it to false.

If this isn't sorting the issue then post your code please.

Here is the code I used. Probably not the best way but it works.

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

If SaveAsUI = False And ThisWorkbook.Sheets(1).Range("AY11") <> 2 Then

Cancel = True

MsgBox "You cannot save this worksheet"
Else
ThisWorkbook.Sheets(1).Range("AY11") = ""
Cancel = False

End If

End Sub


So when a user submits the sheet based on my template, if AY11 = 1 (ie, one of my drop down lists have been updated), then change the value of AY11 to 2.

This unlocks the save option.

I tested it by adding a button.

Range("AY11") = 2

Before hitting the button, try saving. It shouldn't allow it.

Hit the button.

Try saving now.

Put the first parts in a module. Put the last in ThisWorkbook.
Public tfSave As Boolean

Sub SaveNow()
tfSave = True
ThisWorkbook.Save
tfSave = False
End Sub

Sub Save(tf As Boolean)
tfSave = tf
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If tfSave = False Then
MsgBox "File can not be saved."
Cancel = True
End If
End Sub