Excel Formula

[GRitchie]

Hello,

I'm trying to do data validation on a cell so that the user can only pick from a list, and it forces upper case - however if they enter it in lower case, it won't error it will just convert it?

Is this possible without VB?

[dapaulio]

why do you not want to use VB.

 

its easier with VB.

In your sheet hit Alt + F11

open Sheet1(Sheet1)

paste the following code

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = UCase(Target.Value)
End Sub

 

hey presto

[GRitchie]

It may very well end up being shared around multiple people, and getting people to understand they need to 'Enable Content' to edit some documents is bad enough. Never mind telling them to 'Enable Macros'...

[pcstru]

Exact ... ly.

 

ETA - which I thought might help but ...

 

Why the need for uppercase? If it is to be used as an input further down the chain, perhaps use a second cell to hold the value of the list validated cell converted to uppercase.

 

Another option if users are averse to turning macros on, might be to look at macro signing.

Edited May 18, 2016 by pcstru