Data Validation

Posted by Tim Francis-Wright on April 21, 2000 1:42 PM

I have a number of cells in a workbook with data
validation enabled. In many casesm the validation
references a two-cell range with values "Y" and "N".

On some sheets, Excel will allow the user to enter
"y" or "n"; on others, it demands only upper-case
responses. I can't figure out what makes some
sheets more capitalist than others.

Alternatively, is there a way to convert inputted
text to all caps (other than using the UPPER()
worksheet function in another cell)?

Posted by Celia on April 21, 2000 7:01 PM

Tim
Don't know re capitalist/non-capilist sheets - have you checked all the validation settings?

For converting text to Upper case, I use a toolbar button with the following macro. Please note that this macro deliberately does not work on cells that contain a formula (since otherwise the formula would be removed and replaced with the value).

Sub Upper()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = UCase(Rng.Value)
End If
Next Rng
End Sub

Macros for Lower and Proper can be done by substituting Rng.Value = UCase(Rng.Value) with :-

Rng.Value = LCase(Rng.Value)

Rng.Value = StrConv(Rng.Value, vbProperCase)

Celia

Posted by Joe on April 24, 2000 8:08 AM

Tim,
I'm not sure if this is your problem, but I've noticed that if the validation refers to a range that contains the choices, entries in lower case are accepted. However, if the validation choices are listed directly in the validation box (i.e. "Y, N" typed in the "Source" box), lower case entries are not allowed.

Joe

Posted by Tim Francis-Wright on April 24, 2000 8:30 AM

I found a workaround for now. I took the data range on one of the "capitalist" sheets and named it YN. I then set the range for the data validation on each of the worksheets to be YN (they had all referred to a $A$1:$A$2 type range.

This worked (copying a capitalist range to the other validation ranges had not worked), but I'm still puzzled about what made some--but not all--of the sheets demand capital letters.