Inputting cells data as a upper case letters

[vijay.vizzu]

Hi..


I want to know, how to prevent users to input smaller case letters in a cell? Only the upper case letters has to be validated in that column. So please help me..


thanks in advance

 

[oldchippy]

Hi


Take a look here


http://www.dailydoseofexcel.com/archives/2006/10/03/force-upper-case/

 

[SirJB7]

Hi, vijay.vizzu!

Other method is adding this VBA code to your worksheet (Alt-F11):

-----

[pre]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
If Application.WorksheetFunction.IsText(.Value) And .HasFormula = False Then
.Value = UCase(.Value)
End If
End With
Application.EnableEvents = True
End Sub

[/pre]
-----

Regards!

 

[vijay.vizzu]

Thanks for your opinions, but can it possible to use in "List" & "Custom" functions at the same time in validation. because i want to validate two words like "AMORTIZE" & "CAPITALIZE" in that column, and that words should be entered in UPPER CASE. By the above procedure we can validate the cells for UPPER CASE, but i can't able to set the VALIDATATION DATA like amortize & capitalize.


Any help would be appreciated

 

[SirJB7]

Hi, vijay.vizzu!

In the previous code every inputted cell in the worksheet that doesn't have a formula is converted to uppercase, if it contains text values.

If you make 2 things (one, adding this event code to the worksheet code; two, setting cell A1 (for example) validation to B1:B2 (containing respectively CAPITALIZE and AMORTIZE)) when you input capitalize or amortize (in lower or proper case) data validation will accept them, and the worksheet change event code will upper case the entry.

Is that what you're looking for? If not, please upload a file commented with data samples of input and required validation and transformation.

Regards!