Preventing closing a workbook

[PeakyBlinder]

Evening guys, 1st post on here so please be gentle!

I have read various post now, but have not been successful in what I am wanting to achieve. To put it in simple terms I wan to prevent the closing of a workbook if Sheet1 A1 does not contain the word 'Yes'.

All the post I have found have managed to show a prompt to highlight to the user that the cell doesnt contain 'yes' but once 'OK' is selected it closes the workbook. I am wanting the workbook to to be able to close until A1 contains 'yes'

Hope this makes sense and is possible, I have spent that many hours googling and trying my eyes are stinging

Mr Shelby

[Logit]

.

Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Sheets("Sheet1").Range("A1") = "Yes" Then
    Application.DisplayAlerts = False
    
        Application.Visible = False
        ThisWorkbook.Close savechanges:=True
        Application.Quit
    Application.DisplayAlerts = True
    
    Else
    MsgBox "Check value A1"
        Cancel = True
    End If
    
    
End Sub

[PeakyBlinder]

.

Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Sheets("Sheet1").Range("A1") = "Yes" Then
    Application.DisplayAlerts = False
    
        Application.Visible = False
        ThisWorkbook.Close savechanges:=True
        Application.Quit
    Application.DisplayAlerts = True
    
    Else
    MsgBox "Check value A1"
        Cancel = True
    End If
    
    
End Sub

Hi, Many thanks for your reply but unfortunately this has the same result as my current code, whereby it displays the alert, but once 'OK' is selected it just closes, therefore if a user has populated a large quantity of data, but failed to populate A1, all this data would be lost.

S

[gmayor]

While I would suggest using

If LCase(Sheets("Sheet1").Range("A1") = "yes") Then

as the check is case sensitive, the process certainly works here, both with the attached file and with my own test file. I would have posted something similar but Logit was quicker on the draw.

[snb]

As soon as the user disables macros all code is in vain.

So what's the point in preventing the user to close the workbook ?

[Logit]

.
The logic of the macro is to close the workbook ONLY if "Yes" is present in A1.

When running the macro here and "Yes" is not present in A1, once the OK button is clicked in the MsgBox the workbook remains open for editing.

If the workbook closes for you, when you click OK on the MsgBox, something is wrong with your workbook or your machine.

[PeakyBlinder]

Evening guys

Many thanks for all your comments, but I have still not managed to get this to work yet.

Hopefully I have managed to attached my workbook and would be most appreciative if someone could see where I am going wrong. A couple of the sheets link to an external database so obviously those links will be broken. I simplified my initial question for ease of understanding it, but if you look at the 'Programs' sheet, the code is based on the results shown in cell N7 (this is a formula based on the results of other cells)

Hope that makes sensePRP_Sheet.xlsm

[Logit]

.
Strange. Your workbook works as intended here without changing anything.

????

[PeakyBlinder]

mmm very confused, as I have just RDP'd onto a server (so not working on my PC) and it doesnt work!

Well I think you guys have helped enough, obviously something wrong at my our end!