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

 

[Lewiy]

Maybe something along these lines? You can add in as many if statements as you require and obviously edit ranges and Message Box text accordingly:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
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]

Thanks I have done that part of the coding but i wanted it
so that if they try to close the workbook then the messages would flash up
if they have not completed the fields

 

[Lewiy]

That is exactly what that code will do. If the cell has not been filled in, a message box will pop up advising of the empty cell and the workbook will not close. Am I missing something?

 

[divapaxo]

Sorry i think i didn't explain correctly

I have written then code in the macro screen but i am unsure of how
to attach this to the workbook(Sheet) its self.
Do i need to add a command button or something

 

[Lewiy]

Oh, ok, i'm with you now

Right-click on the little Excel icon to the left of the file menu in the top left of the screen and select View Code. This will open the “ThisWorkbook” module. Paste the code in there.

 

[divapaxo]

Thank you for your patience and replies.
I have now done that but still when i choose File/Close
it does not appear to be looking at the macro

 

[Lewiy]

Try this:

MsgBox "The code is running"
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
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

When you close, you should get a message appear saying "The code is running". If you do, then the code is definately running but possibly the cells are not empty so it is checking them and it thinks they are fine.

If you do not get the message then it is possible that Events have been turned off, in which case I can advise further.

 

[divapaxo]

Hi

I would be grateful for your help further as i do not get any messages
saying that the macro etc is running and the only thing i have in each
of my cells is formatted colours

 

[Lewiy]

Ok, open up the Visual Basic Editor as before (right-click on the Excel icon, etc.).

Then hit Ctrl+G, this should open a window called “Immediate”. Scroll down to the bottom of this window and type in:

?Application.EnableEvents

Then hit enter. If it returns “True” then this is not the problem. However, if it returns “False” then in the same window type:

Application.EnableEvents = True

And hit enter again, then try running the code again.

The other possible explanation is that you are in “Design View” or you have macros disabled (possibly because your security level is set too high).