Mandatory fields

[Neilf01]

I am wondering how/if i can make a field in excel (2007) a mandatory field so that a date has to be entered into it?

 

[moonfish]

Select the cell, then go to Data > Data Validation. You can set the validation criteria to contain only date values.

Is that what you meant?

 

[Neilf01]

Thanks Moonfish. That was part of what i meant. I also meant if it was possible to 'force' the user in the spreadsheet to have to fill in a cell with a specific option i.e. a date, number etc?

 

[moonfish]

At which point does it have to be filled in?

Possible answers:
Immediately after the file is opened;
When the file is saved;
No other actions allowed in the worksheet whatsoever, unless the cell contains a value in a specified format;
If another field in the same row/column also contains a value.

Your answer might be different from my examples. Each answer has an entirely different approach to it.

 

[Neilf01]

Hi Moonfish,

It would be if another cell in the same row has a value entered into it. And also (if possible, as a backup) before the spreadsheet can be saved?

Thanks

 

[moonfish]

Here's a macro that you can use to prevent saving if certain fields have not been filled in yet.

It assumes there is a cell for counting the number of missing data in cell A1 on sheet sheet1.
If you don't know how to count the missing values, please provide a sample of your data.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
    If Me.Worksheets("sheet1").Range("A1").Value > 0 Then
        Cancel = True
        MsgBox ("Save cancelled. Please fill in all the required fields and try again.")
    End If
End If
End Sub

 

[Neilf01]

Thanks Moonfish. I am not great on excel and am not really sure what this all means (apologies). I have managed to access the macros and run the macro, beyond that am not sure how to ensure that the field is filled in before saving. Below is example of data:-
<TABLE style="WIDTH: 829pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1105><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5668" width=155><COL style="WIDTH: 148pt; mso-width-source: userset; mso-width-alt: 7204" width=197><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5083" width=139><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6546" width=179><TBODY><TR style="HEIGHT: 18.95pt; mso-height-source: userset" height=25><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 18.95pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=25 width=64>ab</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 36pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=48></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 116pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=155>discs</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 148pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=197>discs</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 92pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=122 align=right>123</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 104pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=139>Dave</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 82pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=109>N/A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=92>8.7.11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 134pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=179></TD></TR></TBODY></TABLE>
I would want the spreadsheet to ensure that there is a date entered in the next cell after the date entered here of 8.7.11?

 

[Neilf01]

Hi Moonfish - got a little further but entering code comes up with "compile error"?

 

[moonfish]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	slot1	slot2	slot3	slot4	slot5	slot6	slot7	slot8	CHECK	*	COUNT
2	ab	*	discs	discs	123	Dave	N/A	8/7/2011	0	*	1
3	test	test	*	*	*	*	*	*	1	*	*
4	*	*	*	*	*	*	*	*	0	*	*
Sheet2

The CHECK column checks whether or not the data in the relevant row is complete. The COUNT cell counts the number of missing dates based on this CHECK column.

If you take the macro I posted earlier and replace A1 with H2 and replace sheet1 with the full sheet name, it should work.

 

[Neilf01]

Hi Moonfish, been trying what you said last and certain things are fine...am not sure how to ensure that the field needs to be filled in though.

Private Sub CommandButton1_Click()
Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
If Me.Worksheets("Book1").Range("J2").Value > 0 Then
Cancel = True
MsgBox ("Save cancelled. Please fill in all the required fields and try again.")
End If
End If
End Sub

Above is the macro that i did from what you advised prevuiously (but am not sure how this is working as i am able to save/close the workbook without the required field being populated?

There is a "command button" on the spreadsheet now as per the macro?