Data Validation twist

[Carim]

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

[Carim]

Sorry ...

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

Thanks

[Peter T]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Tom Ogilvy]

No.

--
Regards,
Tom Ogilvy

"Carim" <[email protected]> wrote in message
news:[email protected]...
> Sorry ...
>
> I meant without VBA ...
> otherwise with worksheet change event
> ActiveCell.Value = Left(ActiveCell.Value, 1)
>
> Thanks
>

[Carim]

Peter,

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

[Carim]

Thanks Tom ...

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

Cheers
Carim

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> Peter,
>
> Thanks a lot.
> I did not know about the validation.type
> Your fix is perfect.
> Thanks again.
> Cheers
> Carim
>

[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