mandatory field set up

divapaxo

Board Regular
Joined
Feb 4, 2005
Messages
110
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
:confused:
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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:
Code:
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

Board Regular
Joined
Feb 4, 2005
Messages
110
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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

Board Regular
Joined
Feb 4, 2005
Messages
110
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
:confused:
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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

Board Regular
Joined
Feb 4, 2005
Messages
110
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Try this:
Code:
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

Board Regular
Joined
Feb 4, 2005
Messages
110
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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:
Code:
?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:
Code:
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).
 

Forum statistics

Threads
1,181,055
Messages
5,927,861
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top