Cell Restrictions
 

How do you restrict a cell so that only certain data can be entered?

For example: I need to protect a cell in a fashion that only a date can
be entered.

I'm having problems with people entering formulas in for the date and I
want to restrict them from doing this (it messes up everything).

Any help would be appreciated

thanks

Cell Restrictions
 

You want to add cell validationl. Select Data - Validation and in the drop
down list select the data type you want. On the other tabs you can define how
you would like to handle input errors...
--
HTH...

Jim Thomlinson


"SmartyPants" wrote:

How do you restrict a cell so that only certain data can be entered?

For example: I need to protect a cell in a fashion that only a date can
be entered.

I'm having problems with people entering formulas in for the date and I
want to restrict them from doing this (it messes up everything).

Any help would be appreciated

thanks

Cell Restrictions
 

I have a similar question.
How do you restrict a text or comobox control, on a user form, so that only
certain data can be entered?



"Jim Thomlinson" wrote:

You want to add cell validationl. Select Data - Validation and in the drop
down list select the data type you want. On the other tabs you can define how
you would like to handle input errors...
--
HTH...

Jim Thomlinson


"SmartyPants" wrote:

How do you restrict a cell so that only certain data can be entered?

For example: I need to protect a cell in a fashion that only a date can
be entered.

I'm having problems with people entering formulas in for the date and I
want to restrict them from doing this (it messes up everything).

Any help would be appreciated

thanks

Cell Restrictions
 

Jim Thomlinson wrote:
You want to add cell validationl. Select Data - Validation and in the drop
down list select the data type you want. On the other tabs you can define how
you would like to handle input errors...
--
HTH...

Jim Thomlinson


This almost works, except I don't see a way to restrict users from
entering the date in the form of a formula.

I restricted the cell to contain a date, but you can still enter the
formula =today().

This is the formula that is causing all my problems.

Cell Restrictions
 

That was my thought when I read data validation as the suggested solution.

You would probably need to use an event macro, with the caution that if
macros are disabled, your protection evaporates. I assume by posting here
that you feel you need a macro and are familiar with macros.

right click on the sheet tab and select view code.

in the left dropdown at the top of the resulting module, select worksheet
and from the dropdown on the right of the resulting module select Change

Private Sub Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

will be placed in the module (Make sure to use Change, not SelectionChange)

then add code like (below is untested pseudo code)

Private Sub Worksheet_Change(ByVal Target As Range)
if Target.count 1 then exit sub
on Error goto ErrHandler
if Target.Address = "$D$4" then
if target.hasformula then
application.EnableEvents = False
Target.clearcontents
Msgbox "No formulas please!"
end if
End if
ErrHandler:
Application.EnableEvents = True

End Sub

this only protect D4 as written. If you need to protect a variety of cells
with the same restrictions you could change the condition to

if not Interset(Target,Range("D2:D10,F4,G5,H3")) is nothing then

as an example.

--
Regards,
Tom Ogilvy


"SmartyPants" wrote:


Jim Thomlinson wrote:
You want to add cell validationl. Select Data - Validation and in the drop
down list select the data type you want. On the other tabs you can define how
you would like to handle input errors...
--
HTH...

Jim Thomlinson


This almost works, except I don't see a way to restrict users from
entering the date in the form of a formula.

I restricted the cell to contain a date, but you can still enter the
formula =today().

This is the formula that is causing all my problems.

Cell Restrictions
 

there is no built in provision for this. You can use events like the change
event, afterupdate, or exit events to have your code check the entry and take
the appropriate action. Just note the change fires on every keystroke.

--
Regards,
Tom Ogilvy



"ernie" wrote:

I have a similar question.
How do you restrict a text or comobox control, on a user form, so that only
certain data can be entered?



"Jim Thomlinson" wrote:

You want to add cell validationl. Select Data - Validation and in the drop
down list select the data type you want. On the other tabs you can define how
you would like to handle input errors...
--
HTH...

Jim Thomlinson


"SmartyPants" wrote:

How do you restrict a cell so that only certain data can be entered?

For example: I need to protect a cell in a fashion that only a date can
be entered.

I'm having problems with people entering formulas in for the date and I
want to restrict them from doing this (it messes up everything).

Any help would be appreciated

thanks

Cell Restrictions
 

thanks alot


"Tom Ogilvy" wrote:

there is no built in provision for this. You can use events like the change
event, afterupdate, or exit events to have your code check the entry and take
the appropriate action. Just note the change fires on every keystroke.

--
Regards,
Tom Ogilvy



"ernie" wrote:

I have a similar question.
How do you restrict a text or comobox control, on a user form, so that only
certain data can be entered?



"Jim Thomlinson" wrote:

You want to add cell validationl. Select Data - Validation and in the drop
down list select the data type you want. On the other tabs you can define how
you would like to handle input errors...
--
HTH...

Jim Thomlinson


"SmartyPants" wrote:

How do you restrict a cell so that only certain data can be entered?

For example: I need to protect a cell in a fashion that only a date can
be entered.

I'm having problems with people entering formulas in for the date and I
want to restrict them from doing this (it messes up everything).

Any help would be appreciated

thanks