Forced Cell Input in Excel 2002
 

I'm working on a spreadsheet that is shared by dozens of users, but the data
input is reviewed by a few of us. To make our trip through the data quicker,
I'm looking for a way to generate an error if they do not fill in certain
fields in the document. Some of the fields are optional, but some should be
mandatory. How do I force them to fill out those fields or get an error
message?

Thanks

Forced Cell Input in Excel 2002
 

You could create a BeforeSave Event macro that could check for proper entries.

Update this if you find a macro acceptable.
--
Gary's Student
gsnu200706


"Josh" wrote:

I'm working on a spreadsheet that is shared by dozens of users, but the data
input is reviewed by a few of us. To make our trip through the data quicker,
I'm looking for a way to generate an error if they do not fill in certain
fields in the document. Some of the fields are optional, but some should be
mandatory. How do I force them to fill out those fields or get an error
message?

Thanks

Forced Cell Input in Excel 2002
 

I have no problem using Macros, but I'm not very familiar with them. I'm
assuming with the name that this would force the cells at the time of save?

Is there another way to do this by looking for other cells in the row? If
those cells (or one of those cells) is filled in, but the cell in question is
not an error message appears?

If not, I'd be interested in learning about this macro.

Thanks

"Gary''s Student" wrote:

You could create a BeforeSave Event macro that could check for proper entries.

Update this if you find a macro acceptable.
--
Gary's Student
gsnu200706


"Josh" wrote:

I'm working on a spreadsheet that is shared by dozens of users, but the data
input is reviewed by a few of us. To make our trip through the data quicker,
I'm looking for a way to generate an error if they do not fill in certain
fields in the document. Some of the fields are optional, but some should be
mandatory. How do I force them to fill out those fields or get an error
message?

Thanks

Forced Cell Input in Excel 2002
 

This is just a sample, not a full solution:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set r = Sheets("Sheet1").Range("A1")
If IsEmpty(r.Value) Then
MsgBox ("You must fill in A1 on Sheet1")
Cancel = True
End If
End Sub

Once installed,the macro will run before the worksheet is Saved or SaveAs.
As coded it check that cell A1 on Sheet1 has not been left blank. If the
macro finds that A1 has been left blank, an error message is issued and the
save CANCELLED.

This is an Event Macro. To learn about Event Macros, see:

http://www.mvps.org/dmcritchie/excel/event.htm

for learning about macros in general see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


If you have a problem or need to customize the sample, don't panic.....just
update this post again.
--
Gary's Student
gsnu200706


"Josh" wrote:

I have no problem using Macros, but I'm not very familiar with them. I'm
assuming with the name that this would force the cells at the time of save?

Is there another way to do this by looking for other cells in the row? If
those cells (or one of those cells) is filled in, but the cell in question is
not an error message appears?

If not, I'd be interested in learning about this macro.

Thanks

"Gary''s Student" wrote:

You could create a BeforeSave Event macro that could check for proper entries.

Update this if you find a macro acceptable.
--
Gary's Student
gsnu200706


"Josh" wrote:

I'm working on a spreadsheet that is shared by dozens of users, but the data
input is reviewed by a few of us. To make our trip through the data quicker,
I'm looking for a way to generate an error if they do not fill in certain
fields in the document. Some of the fields are optional, but some should be
mandatory. How do I force them to fill out those fields or get an error
message?

Thanks

Forced Cell Input in Excel 2002
 

Josh

You could use Conditional Formatting to color cells until they are filled in.

You would base this contingent upon if there is a value in one cell in the row
then the yet-to-be-filled-in cells would change color until they too are filled
in.

Users can choose to ignore the colored cells so this is a visual rminder only.

The alternative BeforeSave code proposed by G's S would prevent saving the
workbook until the cells are filled in by popping up a message saying "fill in
A1, A2 and A3 before saving"


Gord Dibben MS Excel MVP


On Thu, 15 Feb 2007 10:49:10 -0800, Josh wrote:

I have no problem using Macros, but I'm not very familiar with them. I'm
assuming with the name that this would force the cells at the time of save?

Is there another way to do this by looking for other cells in the row? If
those cells (or one of those cells) is filled in, but the cell in question is
not an error message appears?

If not, I'd be interested in learning about this macro.

Thanks

"Gary''s Student" wrote:

You could create a BeforeSave Event macro that could check for proper entries.

Update this if you find a macro acceptable.
--
Gary's Student
gsnu200706


"Josh" wrote:

I'm working on a spreadsheet that is shared by dozens of users, but the data
input is reviewed by a few of us. To make our trip through the data quicker,
I'm looking for a way to generate an error if they do not fill in certain
fields in the document. Some of the fields are optional, but some should be
mandatory. How do I force them to fill out those fields or get an error
message?

Thanks

Forced Cell Input in Excel 2002
 

Thanks, I'll check and see how well this works!

Josh

"Gary''s Student" wrote:

This is just a sample, not a full solution:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set r = Sheets("Sheet1").Range("A1")
If IsEmpty(r.Value) Then
MsgBox ("You must fill in A1 on Sheet1")
Cancel = True
End If
End Sub

Once installed,the macro will run before the worksheet is Saved or SaveAs.
As coded it check that cell A1 on Sheet1 has not been left blank. If the
macro finds that A1 has been left blank, an error message is issued and the
save CANCELLED.

This is an Event Macro. To learn about Event Macros, see:

http://www.mvps.org/dmcritchie/excel/event.htm

for learning about macros in general see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


If you have a problem or need to customize the sample, don't panic.....just
update this post again.
--
Gary's Student
gsnu200706


"Josh" wrote:

I have no problem using Macros, but I'm not very familiar with them. I'm
assuming with the name that this would force the cells at the time of save?

Is there another way to do this by looking for other cells in the row? If
those cells (or one of those cells) is filled in, but the cell in question is
not an error message appears?

If not, I'd be interested in learning about this macro.

Thanks

"Gary''s Student" wrote:

You could create a BeforeSave Event macro that could check for proper entries.

Update this if you find a macro acceptable.
--
Gary's Student
gsnu200706


"Josh" wrote:

I'm working on a spreadsheet that is shared by dozens of users, but the data
input is reviewed by a few of us. To make our trip through the data quicker,
I'm looking for a way to generate an error if they do not fill in certain
fields in the document. Some of the fields are optional, but some should be
mandatory. How do I force them to fill out those fields or get an error
message?

Thanks