How to disable the save dialog box and avoid workbook to close

[gozeimas]

Hi,

I hope somebody is able to help me with the following issue.

I have made a userform in VBA which pops-up as soon as the the Excel workbook tries to close when activating the red X-button. The userform has 3 buttons "Cancel", "Yes", "No".

When button "No" is activated the following must happen: The userform is unloaded and the user is guided to a certain cell to enter further information.
It works except that the normal save dialog ("Save", "Don't save", "Cancel") pops-up. I do NOT want this to happen.

Here is the code I have used so far:
'------ In the userform ------------

Private Sub vbNo_Click()      
       Application.Goto shSTARTSHEET.Range("E93")
       Unload QUOTATIONCHECK
End Sub

'------ ThisWorkbook ------------

Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Integer, CloseMode As Integer)
    QUOTATIONCHECK.Show
End sub

What code can I put in to avoid the save dialog box to come up? (It is OK that it comes up when button "Cancel" is used. When button "Yes" is used I have used a code that will automatically save and close the workbook - this part works OK).

BR

[OllieB]

Set Cancel to True, to cancel the the requested close process

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     QUOTATIONCHECK.Show
     Cancel = True
End Sub

[gozeimas]

Hi OllieB,

your suggestion works.........:-), however now the other buttons "Cancel" and "Yes" is not working correctly. "Cancel" does NOT come up with a pop-up indicating Save, Don't save, Cancel etc.....BUT it should! And button "Yes" does no longer close down the application as expected.

[OllieB]

Ok.

Declare and use a public variable for which you set the value in the form depending on the button clicked by the user. Then in the worksheet_BeforeClose routine you can use the value of that variable to determine whether Cancel should be set to True or False.

[gozeimas]

Hi OllieB,

I am unfortunately unsure what to do here - could you maybe provide an example?
If yes, please indicate what to put where (what code in "Module", what code in "ThisWorkbook", what code in the userform and how the link between these are carried out?

[gozeimas]

I found a solution by adding more VBA code (among other public variables as you mentioned) from another help site - Thx for your support so far.

[OllieB]

Good to hear that you managed to solve it.