stop save changes on exit

[cocopops2001]

hello,

i want my workbook to have a custom 'YES NO' box on exit instead of the Excel Save Changes? box. I have my exit box working fine but when i click NO excel closes down, when i click yes the box reappears and i have to click YES twice. the box is called by workbook_beforeclose

Dim ans As Integer
Sub exitbox()

ans = MsgBox("Do you want to Exit the calculation selector?", vbYesNo, "Exit")
Select Case ans
    Case vbNo
    No = True
    Exit Sub
    Case Else
End Select

    If ans = vbYes Then

    ''''saves visible properties of each sheet
    SaveStateAndHide

    ThisWorkbook.Close Saved = True
    End If

End Sub

any ideas?


EDIT: also when i click no the save changes box still appears? i have turned off application.displayalerts before the exitbox sub runs in the sub 'workbook_beforeclose'

 

[mallard1976]

Can you try this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If MsgBox("Do you want to Exit the calculation selector?", vbYesNo, "Exit") = vbNo Then
        Cancel = True
        
    Else
    
        
        SaveStateAndHide    'not sure what's in here
 
        ThisWorkbook.Save
    End If
End Sub

 

[cocopops2001]

works fine but for some reason i have to click YES twice when i want to exit the book. i have changed it to

workbook.close saved=true

as i do NOT want users to save any data.


EDIT: i know that by calling the close function then it will cause the msgbox to reappear just dont know what to put

 

[cocopops2001]

no more ideas on this one?