DATA REQUIRED IN CERTAIN CELLS
Posted by Dan on January 24, 2002 4:29 AM
I have a form in which some of the cells must contain data. The problem is if you do not enter the cell where you have this validation then when it comes to saving, excel doesnt realise that there should be data in certain cells and just saves the file.
Does anyone know of a way around this.
Thanks
Dan
Posted by Juan Pablo G. on January 24, 2002 5:25 AM
In the Workbook_BeforeSave event, make sure that all your "mandatory" cells have something in them, maybe using something like:
If WorksheetFunction.CountA(Union([B5],[C8],[D6:D8],[E7])) = 6 then
'Everything ok
else
Cancel = True
MsgBox "There are some cells missing"
End If
Juan Pablo G.
Posted by Joe Was on January 24, 2002 7:15 AM
Only test for one cell or range, flag and note.
Juan's code works great for a group of cells and ranges. The code here flags one cell or range with the missing data by coloring the cell light blue and displaying a note indicating the missing cell or range data. If the data is present the cell is colored light yellow (The default color of this data cell or range!) and displays a note that the required data is in place.
The code becomes sloppy if you extend it to other cells or ranges, but you could add additional blocks if needed. If you have many cells and ranges Juan's code is better. JSW
