Excel conditions

On 6 May 2003 11:42:10 -0700, "Lori" <Lori2-1@excite.com> wrote:

>
> I am automating excel and have a cell that I only want the user to be able
>to enter a 1 or 0 in. Hoping someone could tell me how I can set this up.
>I am sending the file to a list of clients and they fill them out and send
>them back. Thanks

Figure out how you'd do this interactively in Excel and then record a
macro to do it. Examine the VBA code to see what you need to automate.

Tamar

Lori <Lori2-1@excite.com> inscribed in
news:3eb80202$1@tnews.web.dev-archive.com ...
> I am automating excel and have a cell that I only want the user to
> be able to enter a 1 or 0 in. Hoping someone could tell me how I can
> set this up. I am sending the file to a list of clients and they fill
> them out and send them back. Thanks

Hi Lori,

You might do better in an Excel-specific newsgroup. However, here's what I
found in the Excel Help (Excel 2003 Beta but it should be the same in
earlier versions) on "Validation"

>>
Select the cell to validate.
On the Data menu, click Validation, and then click the Settings tab.
Specify the type of validation you want:
Allow values from a list
In the Allow box, click List.
Click the Source box and then do one of the following:
To define the list locally, type the list values separated by commas.
To use a range of cells with a defined name, type the equal sign (=)
followed by the name of the range.
To use cell references, select the cells on the worksheet and then press
ENTER.
Note Cell references have to be to cells on the same worksheet. If you have
to refer to a different worksheet or a different workbook, use a defined
name and make sure the workbook is already open.
Make sure the In-cell dropdown check box is selected.
Allow numbers within limits
In the Allow box, click Whole Number or Decimal.
In the Data box, click the type of restriction you want. For example, to set
upper and lower limits, click between.
Enter the minimum, maximum, or specific value to allow.
Allow dates or times within a timeframe
In the Allow box, click Date or Time.
In the Data box, click the type of restriction you want. For example, to
allow dates after a certain day, click greater than.
Enter the start, end, or specific date or time to allow.
Allow text of a specified length
In the Allow box, click Text Length.
In the Data box, click the type of restriction you want. For example, to
allow up to a certain number of characters, click less than or equal to.
Enter the minimum, maximum, or specific length for the text.
Calculate what's allowed based on the content of another cell
In the Allow box, click the type of data you want.
In the Data box, click the type of restriction you want.
In the box or boxes below the Data box, click the cell that you want to use
to specify what's allowed. For example, to allow entries for an account only
if the result won't go over the budget, click Decimal for Allow, click less
than or equal to for Data, and in the Maximum box, click the cell that
contains the budget amount.
Use a formula to calculate what's allowed
In the Allow box, click Custom.
In the Formula box, enter a formula that calculates a logical value (TRUE
for valid entries or FALSE for invalid). For example, to allow the value in
the cell for the picnic account only if nothing is budgeted for the
discretionary account (cell D6) and the total budget (D20) is also less than
the $40,000 allocated, you could enter =AND(D6=0,D20<40000) for the custom
formula.
Specify whether the cell can be left blank:
If you want to allow blank (null) values, select the Ignore blank check box.
If you want to prevent entry of blank (null) values, clear the Ignore blank
check box.
Note If your allowed values are based on a cell range with a defined name,
and there is a blank cell anywhere in the range, setting the Ignore blank
check box allows any values to be entered in the validated cell. This is
also true for any cells referenced by validation formulas: if any referenced
cell is blank, setting the Ignore blank check box allows any values to be
entered in the validated cell.
To display an optional input message when the cell is clicked, click the
Input Message tab, make sure the Show input message when cell is selected
check box is selected, and fill in the title and text for the message.
Specify how you want Microsoft Excel to respond when invalid data is
entered:
How?
Click the Error Alert tab, and make sure the Show error alert after invalid
data is entered check box is selected.
Select one of the following options for the Style box:
To display an information message that does not prevent entry of invalid
data, click Information.
To display a warning message that does not prevent entry of invalid data,
click Warning.
To prevent entry of invalid data, click Stop.
Fill in the title and text for the message (up to 225 characters).
Note If you don't enter a title or text, the title defaults to "Microsoft
Excel" and the message to: "The value you entered is not valid. A user has
restricted values that can be entered into this cell."
<<

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden@mvps.org http://cindywinegarden.adsl.duke.edu
http://msdn.microsoft.com/vfoxpro http://foxcentral.net