Disable x-button

[gandalf]

I'm looking on the board for more than half a day and it still doesn't work for me. I use MS Excel 97. I want to disable the x-button (to close the file) when a certain field (D10) has a value in it when the document is opened. When D10 is empty when opening the file and you start putting values in (also in D10) and you want to close the file I want a messagebox to pop up that lets you choose between closing without saving or Cancelling tha action so you stay in the current file.

What do you guys mean by "Put it in a Userform"? How do you do that? Please explain it step by step because I tried some of the codes in Modules, in VBA Workscheet, in "This Workbook" and it just doesn't work. Please can anyone help me out. THANKS!

 

[lenze]

I'm not sure exactly what you want, but maybe this is close. Place these codes in the This WorkBook module

Public MyCell As Variant

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If myTest = True Then
Cancel = True
Exit Sub
End If

Dim Resp As Integer
If Range("$D$10").Value <> MyCell Then
Resp = MsgBox("Cell D10 has been altered. Do you wish to close the file.", vbYesNo)
Else: Cancel = False
End If
Select Case Resp
Case Is = 6
Exit Sub

Case Is = 7
Cancel = True

End Select

End Sub

Private Sub Workbook_Open()
If Range("$D$10").Value <> "" Then myTest = True
If Range("$D$10").Value = "" Then myTest = False
MyCell = Range("$D$10").Value
End Sub

I think this does what you asked. The problem is, if D10 has a value in it when opened, you CANNOT close the file. So, you need a special macro to run to close it. Put this in a regular module

Public myTest As Boolean
Sub SpecialClose()
myTest = False
ActiveWorkbook.Close
End Sub

Hope this helps get you started

 

[gandalf]

It got me started, thanks for that, especially your clear statements where I had to put the different "subs". But I'm still not completely satisfied. When I click the x-button a box appears asking if I wanna close the file. When I choose "No" I stay in the document and that is fine but when I choose "Yes" I want the document to CLOSE WITHOUT SAVING AND WITHOUT ASKING ME IF I WANNA SAVE THE FILE. Can you help me with this one? I would highly appreciate it. THANKS.

 

[lenze]

Enter this line of code in the Before Close routine After the "Case is = 6"

ActiveWorkBook.Saved = True

Note: Don't forget to do a Save before you clsoe the book the first time

 

[gandalf]

lenze, thanks for all the help so far but it still doesn't work as I would like. I 'll go and try again to explain my problem.

I have created a macro to register a file. If the originator of the file press the register-button then the file will be saved, put on an overview list en mailed to one or more recipients BUT if the originator wants to close the file using the x-button he must get a messagebox that asks him if he wants to close the file without saving anything(the file closes without (automatically) saving or without asking if he wants to save the changes) or if he wants to stay in the file (also without saving). If he wants his changes saved he has to use the former mentionned register-button.