Im trying to set up a columnvalidation using a list. In the list I have DKK
and EUR but I would like for my users to be able to enter lowercase values
like dkk and eur. But it looks like I have to extend my list to include the
lowercase values? But this makes my dropdown look... well I would like the
dropdown list to be just the uppercase values but still allow for the users
to enter lowercase. I would then uppercase all values on saving the
document.

Is this possible?

Jan Agermose

Jan

That would be a nice feature, but I don't think it's possible currently.

If you want to do some fancy programming, you may be able to emulate that
behavior using the Worksheet_Change event. Post back if you're interested
in that and I'll see what I can come up with.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Jan Agermose" wrote in message
...
Im trying to set up a columnvalidation using a list. In the list I have
DKK
and EUR but I would like for my users to be able to enter lowercase values
like dkk and eur. But it looks like I have to extend my list to include
the
lowercase values? But this makes my dropdown look... well I would like the
dropdown list to be just the uppercase values but still allow for the
users
to enter lowercase. I would then uppercase all values on saving the
document.

Is this possible?

Jan Agermose

Yes, please, I would be very interested.

--
Jan


"Dick Kusleika" wrote in message
...
Jan

That would be a nice feature, but I don't think it's possible currently.

If you want to do some fancy programming, you may be able to emulate that
behavior using the Worksheet_Change event. Post back if you're interested
in that and I'll see what I can come up with.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Jan Agermose" wrote in message
...
Im trying to set up a columnvalidation using a list. In the list I have
DKK
and EUR but I would like for my users to be able to enter lowercase
values
like dkk and eur. But it looks like I have to extend my list to include
the
lowercase values? But this makes my dropdown look... well I would like
the
dropdown list to be just the uppercase values but still allow for the
users
to enter lowercase. I would then uppercase all values on saving the
document.

Is this possible?

Jan Agermose

Jan

In your Data Validation, go to the Error Alert and uncheck Show Error
After...

Right click on the sheet tab and choose view code.

Paste the below sub into the resulting code pane.

Close the VBE to return to Excel.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ValList As Variant
Dim RngList As String
Dim cell As Range
Dim i As Long
Dim InvalidEntry As Boolean

On Error Resume Next

If Left(Target.Validation.Formula1, 1) = "=" Then
For Each cell In Range(Replace(Target.Validation.Formula1, "=",
"")).Cells
RngList = RngList & cell.Value & ","
Next cell
RngList = Right(RngList, Len(RngList) - 1)
ValList = Split(RngList, ",")
Else
ValList = Split(Target.Validation.Formula1, ",")
End If

If Err.Number = 0 Then

On Error GoTo 0

If Target.Validation.Type = xlValidateList Then

InvalidEntry = True

For i = LBound(ValList) To UBound(ValList)
If UCase(Target.Value) = UCase(ValList(i)) Then
InvalidEntry = False
Exit For
End If
Next i

If InvalidEntry Then
MsgBox "The entry is invalid"
Target.Select
Application.SendKeys "{F2}"
Else
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End If
End If
End If

End Sub

By turning off the error alert, we're allowing the user to enter anything
they want and then validating it in the Worksheet_Change event. This allows
the user to enter lower case and the sub changes it to upper case if it
matches anything in the validation list.

Try it out an let me know if you run into any problems.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Jan Agermose" wrote in message
...
Yes, please, I would be very interested.

--
Jan


"Dick Kusleika" wrote in message
...
Jan

That would be a nice feature, but I don't think it's possible currently.

If you want to do some fancy programming, you may be able to emulate
that
behavior using the Worksheet_Change event. Post back if you're
interested
in that and I'll see what I can come up with.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Jan Agermose" wrote in message
...
Im trying to set up a columnvalidation using a list. In the list I
have
DKK
and EUR but I would like for my users to be able to enter lowercase
values
like dkk and eur. But it looks like I have to extend my list to
include
the
lowercase values? But this makes my dropdown look... well I would like
the
dropdown list to be just the uppercase values but still allow for the
users
to enter lowercase. I would then uppercase all values on saving the
document.

Is this possible?

Jan Agermose