I am creating an excel spreadsheet different departments will be using and
want to ensure they enter information in certain cells. Is there a way I can
create a rule or validation to make a message come up if they don't fill in
a particular cell?

In the Thisworkbook module: ** Sample Data Only** Change Rng addresses to suit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set Rng = Range("B8,B11,E11,B14,E14")
For Each c In Rng
If c = "" Then MsgBox "Please Fill In Cell " & c.Address & " before
Closing."
If c = "" Then Cancel = True
Next c

End Su

"JoeP" wrote:

I am creating an excel spreadsheet different departments will be using and
want to ensure they enter information in certain cells. Is there a way I can
create a rule or validation to make a message come up if they don't fill in
a particular cell?

Looking back... I might should restrict this code to the Specific Sheet that
your
Form is on - so that other sheets are not affected...

So line 2 should probably be:
Set Rng = Worksheets("MyFormSheet").Range("B8,B11,E11,B14,E1 4")

Just now starting to understand this stuff..

Jim

"Jim May" wrote:

In the Thisworkbook module: ** Sample Data Only** Change Rng addresses to suit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set Rng = Range("B8,B11,E11,B14,E14")
For Each c In Rng
If c = "" Then MsgBox "Please Fill In Cell " & c.Address & " before
Closing."
If c = "" Then Cancel = True
Next c

End Su

"JoeP" wrote:

I am creating an excel spreadsheet different departments will be using and
want to ensure they enter information in certain cells. Is there a way I can
create a rule or validation to make a message come up if they don't fill in
a particular cell?

OK, this is it << I think

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ActiveSheet.Name = Sheets("Sheet1").Name Then
Set Rng = Sheets("Sheet1").Range("B8,B11,E11,B14,E14")
For Each c In Rng
If c = "" Then MsgBox "Please Fill In Cell " & c.Address & " before
Closing."
If c = "" Then Cancel = True
Next c
End If
End Sub

"Jim May" wrote:

In the Thisworkbook module: ** Sample Data Only** Change Rng addresses to suit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set Rng = Range("B8,B11,E11,B14,E14")
For Each c In Rng
If c = "" Then MsgBox "Please Fill In Cell " & c.Address & " before
Closing."
If c = "" Then Cancel = True
Next c

End Su

"JoeP" wrote:

I am creating an excel spreadsheet different departments will be using and
want to ensure they enter information in certain cells. Is there a way I can
create a rule or validation to make a message come up if they don't fill in
a particular cell?

Hi,

Plese refer to the following link:

http://www.contextures.com/xlDataVal02.html

Challa Prabhu

"JoeP" wrote:

I am creating an excel spreadsheet different departments will be using and
want to ensure they enter information in certain cells. Is there a way I can
create a rule or validation to make a message come up if they don't fill in
a particular cell?

Thanks for the help. I looked at the link--some great stuff--but I cannot
find information on validating to ensure that a cell(s) is not empty.

Could you point me to the info.

Thanks.

JoeP

"challa prabhu" wrote:

Hi,

Plese refer to the following link:

http://www.contextures.com/xlDataVal02.html

Challa Prabhu

"JoeP" wrote:

I am creating an excel spreadsheet different departments will be using and
want to ensure they enter information in certain cells. Is there a way I can
create a rule or validation to make a message come up if they don't fill in
a particular cell?

Joe

If the users ignore the cell you want filled, there is no way to force them to
fill it. DV works only when an attempt to enter data is performed.

In a cell adjacent to the fill-in cells you could have a formula like

=IF(A1="","Please fill-in A1 before moving on","A1 is OK") as a reminder

You could place conditional formatting on the cell to have it colored until
users enter data.

Just as a reminder, of course, not as a way to enforce entering data.

Alternative................

You could use workbook beforeclose code to stop the closing and saving of the
workbook if certain cells are not filled, along with a message reminding them to
go back and do it right.

Then they will screw you over by disabling macros when opening the workbook.

Then you will have to deal with that event.

Easy huh?


Gord Dibben MS Excel MVP


On Tue, 31 Jul 2007 13:56:04 -0700, JoeP wrote:

Thanks for the help. I looked at the link--some great stuff--but I cannot
find information on validating to ensure that a cell(s) is not empty.

Could you point me to the info.

Thanks.

JoeP

"challa prabhu" wrote:

Hi,

Plese refer to the following link:

http://www.contextures.com/xlDataVal02.html

Challa Prabhu

"JoeP" wrote:

I am creating an excel spreadsheet different departments will be using and
want to ensure they enter information in certain cells. Is there a way I can
create a rule or validation to make a message come up if they don't fill in
a particular cell?

Is there a way to validate more than one criteria on a cell? For example I
want to validate that a cell is all caps and has 25 or less characters.

Thanks

"Gord Dibben" wrote:

Joe

If the users ignore the cell you want filled, there is no way to force them to
fill it. DV works only when an attempt to enter data is performed.

In a cell adjacent to the fill-in cells you could have a formula like

=IF(A1="","Please fill-in A1 before moving on","A1 is OK") as a reminder

You could place conditional formatting on the cell to have it colored until
users enter data.

Just as a reminder, of course, not as a way to enforce entering data.

Alternative................

You could use workbook beforeclose code to stop the closing and saving of the
workbook if certain cells are not filled, along with a message reminding them to
go back and do it right.

Then they will screw you over by disabling macros when opening the workbook.

Then you will have to deal with that event.

Easy huh?


Gord Dibben MS Excel MVP


On Tue, 31 Jul 2007 13:56:04 -0700, JoeP wrote:

Thanks for the help. I looked at the link--some great stuff--but I cannot
find information on validating to ensure that a cell(s) is not empty.

Could you point me to the info.

Thanks.

JoeP

"challa prabhu" wrote:

Hi,

Plese refer to the following link:

http://www.contextures.com/xlDataVal02.html

Challa Prabhu

"JoeP" wrote:

I am creating an excel spreadsheet different departments will be using and
want to ensure they enter information in certain cells. Is there a way I can
create a rule or validation to make a message come up if they don't fill in
a particular cell?

You could use
Formula is:
=and(len(a1)<=25,exact(upper(a1),a1))

carloss wrote:

Is there a way to validate more than one criteria on a cell? For example I
want to validate that a cell is all caps and has 25 or less characters.

Thanks

"Gord Dibben" wrote:

Joe

If the users ignore the cell you want filled, there is no way to force them to
fill it. DV works only when an attempt to enter data is performed.

In a cell adjacent to the fill-in cells you could have a formula like

=IF(A1="","Please fill-in A1 before moving on","A1 is OK") as a reminder

You could place conditional formatting on the cell to have it colored until
users enter data.

Just as a reminder, of course, not as a way to enforce entering data.

Alternative................

You could use workbook beforeclose code to stop the closing and saving of the
workbook if certain cells are not filled, along with a message reminding them to
go back and do it right.

Then they will screw you over by disabling macros when opening the workbook.

Then you will have to deal with that event.

Easy huh?


Gord Dibben MS Excel MVP


On Tue, 31 Jul 2007 13:56:04 -0700, JoeP wrote:

Thanks for the help. I looked at the link--some great stuff--but I cannot
find information on validating to ensure that a cell(s) is not empty.

Could you point me to the info.

Thanks.

JoeP

"challa prabhu" wrote:

Hi,

Plese refer to the following link:

http://www.contextures.com/xlDataVal02.html

Challa Prabhu

"JoeP" wrote:

I am creating an excel spreadsheet different departments will be using and
want to ensure they enter information in certain cells. Is there a way I can
create a rule or validation to make a message come up if they don't fill in
a particular cell?




--

Dave Peterson

How can I keep a validation when someone pastes data in from another
spreadsheet? Currently when the user pastes in data the validation is lost.

"Dave Peterson" wrote:

You could use
Formula is:
=and(len(a1)<=25,exact(upper(a1),a1))

carloss wrote:

Is there a way to validate more than one criteria on a cell? For example I
want to validate that a cell is all caps and has 25 or less characters.

Thanks

"Gord Dibben" wrote:

Joe

If the users ignore the cell you want filled, there is no way to force them to
fill it. DV works only when an attempt to enter data is performed.

In a cell adjacent to the fill-in cells you could have a formula like

=IF(A1="","Please fill-in A1 before moving on","A1 is OK") as a reminder

You could place conditional formatting on the cell to have it colored until
users enter data.

Just as a reminder, of course, not as a way to enforce entering data.

Alternative................

You could use workbook beforeclose code to stop the closing and saving of the
workbook if certain cells are not filled, along with a message reminding them to
go back and do it right.

Then they will screw you over by disabling macros when opening the workbook.

Then you will have to deal with that event.

Easy huh?


Gord Dibben MS Excel MVP


On Tue, 31 Jul 2007 13:56:04 -0700, JoeP wrote:

Thanks for the help. I looked at the link--some great stuff--but I cannot
find information on validating to ensure that a cell(s) is not empty.

Could you point me to the info.

Thanks.

JoeP

"challa prabhu" wrote:

Hi,

Plese refer to the following link:

http://www.contextures.com/xlDataVal02.html

Challa Prabhu

"JoeP" wrote:

I am creating an excel spreadsheet different departments will be using and
want to ensure they enter information in certain cells. Is there a way I can
create a rule or validation to make a message come up if they don't fill in
a particular cell?




--

Dave Peterson