Prevent Saving

[Excelerr]

Hello, I'm wondering if there is a way to not allow saving in the workbook if the user has not changed the answer from a validated list? I have Yes, No, and Provide-Answer in the data validation list and some VBA code that does not allow saving with an empty cell but I couldn't save when it was left empty so I added the Provide-Answer. Saving with macros not enabled was not working.
Is there anything I can add in VBA or within excel that will not allow saving without changing a "Provide-Answer"?

 

[James006]

Hello,

Have you tested in the module ThisWorkbook :

[B]Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean)
[/B][COLOR=navy][FONT=Courier New] If SaveAsUI = True Then Cancel = True
[/FONT][/COLOR][B]End Sub
[/B]

Hope this will help

 

[Excelerr]

I just tried that, no changes unfortunately.

 

[Yongle]

You can do something like this

List the values that are not acceptable in cell C7, separated by a comma - the workbook will not save if the cell value matches any one of them

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim cel As Range: Set cel = Sheets("Sheet1").Range("C7")
    
        Select Case cel.Value
            Case [COLOR=#ff0000]vbNullString, "disallowed reply", 1[/COLOR]
                GoTo CloseWithoutSave
            Case Else
                Exit Sub
        End Select
        
CloseWithoutSave:
    Cancel = True
    If MsgBox("Close without saving?", vbYesNoCancel, "???") = vbYes Then
        ThisWorkbook.Close False
    Else
        cel.Parent.Activate
        cel.Activate
    End If
End Sub

Goes in ThisWorkbook module

 

[Excelerr]

Trying to test it now and I'm not able to save the workbook at all

 

[Yongle]

Work it out...

 

[Excelerr]

You can do something like this

List the values that are not acceptable in cell C7, separated by a comma - the workbook will not save if the cell value matches any one of them

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim cel As Range: Set cel = Sheets("Sheet1").Range("C7")
    
        Select Case cel.Value
            Case [COLOR=#ff0000]vbNullString, "disallowed reply", 1[/COLOR]
                GoTo CloseWithoutSave
            Case Else
                Exit Sub
        End Select
        
CloseWithoutSave:
    Cancel = True
    If MsgBox("Close without saving?", vbYesNoCancel, "???") = vbYes Then
        ThisWorkbook.Close False
    Else
        cel.Parent.Activate
        cel.Activate
    End If
End Sub

Goes in ThisWorkbook module

This is helpful, could i replace msgbox where closewithoutsave is; ideally I would like an error message to come up and not allow saving, but not close the workbook either.

 

[Yongle]

Try removing the line that closes the workbook and see if that gives you the functionality you want.
If it does, remove the whole of that IF statement and just leave the 2 lines beginning with cel.