Data Validation twist

Hi,

Is there a way, for a data validation based on a List, to only input
the first character of the selected choice ?
TIA
Carim

Sorry ...

I meant without VBA ...
otherwise with worksheet change event
ActiveCell.Value = Left(ActiveCell.Value, 1)

Thanks

No.

--
Regards,
Tom Ogilvy

"Carim" <carim.fam@wanadoo.fr> wrote in message
news:1114769895.169979.70800@g14g2000cwa.googlegroups.com...
> Sorry ...
>
> I meant without VBA ...
> otherwise with worksheet change event
> ActiveCell.Value = Left(ActiveCell.Value, 1)
>
> Thanks
>

Thanks Tom ...

I know the value of your "No"
It saves me a lot of time.

Cheers
Carim

Hi Carim,

This should input the first character in a DV list for any cell on the sheet
that has a DV list.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo done:
If Target(1).Validation.Type = xlValidateList Then
Application.EnableEvents = False
Target(1) = Left(Target(1), 1)
End If
done:
Application.EnableEvents = True
End Sub

If you don't want this to affect all DV list cells on the sheet, you will
need to perform some other check on the Target to limit the change.

Regards,
Peter T


"Carim" <carim.fam@wanadoo.fr> wrote in message
news:1114766192.277945.84890@z14g2000cwz.googlegroups.com...
> Hi,
>
> Is there a way, for a data validation based on a List, to only input
> the first character of the selected choice ?
> TIA
> Carim
>

Peter,

Thanks a lot.
I did not know about the validation.type
Your fix is perfect.
Thanks again.
Cheers
Carim

I'm confused?

I thought you said:

> I meant without VBA ...

then you seemed to say you knew how to do it using an event.

As to Peter's second comment, If you only want to do it on one cell (which
is what you question sounded like), you don't need to even check if it has
validation.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$9" then
if len(Target.Value) > 1 then
Target.Value = Left(Target.Value,1)
End if
end If
End Sub

and for completeness, in xl97, the change event does not fire when a value
is selected from a data validation dropdown when the list is filled from a
worksheet.

--
Regards,
Tom Ogilvy

"Carim" <carim.fam@wanadoo.fr> wrote in message
news:1114774558.745425.72470@l41g2000cwc.googlegroups.com...
> Peter,
>
> Thanks a lot.
> I did not know about the validation.type
> Your fix is perfect.
> Thanks again.
> Cheers
> Carim
>

Tom,

Sorry for my english ... I am a French native.
You are right, I meant I had a feasible with VBA with XL2000, but did
not know how to do it with a "validation-custom-formula" twist ...
Thanks again for your valuable comments
Best Regards
Carim