mandatory field set up

[divapaxo]

Hi All

wonder if anyone can advise if the following is possible in excel

I have a spreadsheet which has several fields containing data and
what i want to achieve is that when someone comes to close the
spreadsheet , If they have not filled in all the fields required it will
put up a notice to say that field **** must be completed.

your help would be much appreciated

thanks

 

[divapaxo]

Thanks for all your help I still cannot get it to come up
when io choose file close.

thanks Again

 

[Lewiy]

What happened when you put the code in the immediate window? What is your security level set to? (Tools>Macro>Security)

 

[divapaxo]

Hi

When i posted the code to the immediate window it worked with the first instance it returned true

and my security level is set to medium (you can choose whether or not to run potentially unsafe macros)

 

[Lewiy]

Doh! I've just realised that my test code was wrong, the messagebox line should be inside the sub:

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
MsgBox "The code is running" 
If Sheets("Sheet1").Range("A1") = "" Then 
    Cancel = True 
    MsgBox "A1 has not been filled in" 
End If 
If Sheets("Sheet1").Range("F10") = "" Then 
    Cancel = True 
    MsgBox "F10 has not been filled in" 
End If 
End Sub

Try that and see if it makes a difference.

 

[divapaxo]

This is the code i have and it is still not working BOO HOO

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "The code is running"
If Sheets("Sheet1").Range("A1") = "" Then
Cancel = True
MsgBox "A1 has not been filled in"
End If
If Sheets("Sheet1").Range("F10") = "" Then
Cancel = True
MsgBox "F10 has not been filled in"
End If
End Sub

 

[divapaxo]

This is the code i have and it is still not working BOO HOO

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "The code is running"
If Sheets("Sheet1").Range("A1") = "" Then
Cancel = True
MsgBox "A1 has not been filled in"
End If
If Sheets("Sheet1").Range("F10") = "" Then
Cancel = True
MsgBox "F10 has not been filled in"
End If
End Sub

 

[Lewiy]

Ok, forgive me for just checking, but the code is definately in the "ThisWorkBook" module and you are leaving A1 and F10 blank to test that it works?

Have you tried opening a brand new workbook and just testing the code in there? This might highlight if there's a problem somewhere in the workbook you are using.

 

[divapaxo]

I have tried the code in a new workbook no joy

I am opening up the workbook and it goes straight into worksheet New Equity Clear (this is where i added the code to the little excel button next to file

Sorry for this i just don't seem to be able to get it to work. when i choose file/close it just goes straight out

 

[Lewiy]

Is the worksheet called "New Equity Clear" the one where you need to validate the cells? If so then you will need to change all references in the code to Sheets("Sheet1") into Sheets("New Equity Clear")

 

[divapaxo]

I have all the code refering to New Equity Clear.

Please see code below

I am really very very grateful for all your help and hope that i can get this resolved.

Private Sub Workbook_NEW_EQUITY_CLEAR_ACCOUNT(Cancel As Boolean)
MsgBox "The Code Is Running"
If Sheets("New Equity Clear").Range("C12") = "" Then
Cancel = True
MsgBox "C12 has not been filled in"
End If
If Sheets("New Equity Clear").Range("C14") = "" Then
Cancel = True
MsgBox "C14 has not been filled in"
End If
If Sheets("New Equity Clear").Range("C16") = "" Then