Changing Case in a Cell

  • Thread starter Thread starter JimS
  • Start date Start date
J

JimS

Is there a way to type the word "yes" in to a cell and have it change
to "YES" uppercase? Without the quotes of course.

Thanks
 
If you don't mind it being in another cell, you can put this formula
in B1:

=IF(A1="","",UPPER(A1))

If you want it to change within A1 once you have entered it, then you
will have to use a macro to do that.

Hope this helps.

Pete
 
Is there a way to type the word "yes" in to a cell and have it change
to "YES" uppercase? Without the quotes of course.

Thanks
Click to expand...

You are very specific in your request. I'm not sure if that is exactly what
you want, but the following will change any lower case or mixed case "yes" that
you type into a cell into an uppercase "YES".

Right click on your worksheet tab and select "View Code"

Paste the code below into the window that opens:

===========================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
For Each c In Target
If InStr(1, c.Formula, "yes", vbTextCompare) > 0 Then
c.Value = Replace(c.Formula, "yes", "YES", 1, -1, vbTextCompare)
End If
Next c
Application.EnableEvents = True
End Sub
============================================

You might want to consider modifying this to include other words; or to be
restricted to just certain cells; or to be restricted to when "yes" is the only
thing typed into the cell; etc.
--ron
 
I should say that whatever I type into the cell I want to change. Not
specifically the word yes. Thanks for the code. How should I change
it?
 
For all cells in columns A:H

Edit the target.column range to suit................

i.e. >1 for column A, >2 for columns A and B

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 8 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP
 
I should say that whatever I type into the cell I want to change. Not
specifically the word yes. Thanks for the code. How should I change
it?
Click to expand...


Something like below. But you will need to adjust the line that starts with

Set r = Range("

to reflect the range you want to process, if that range is limited.

======================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Dim c As Range
Set r = Range("A:B")
Application.EnableEvents = False
If Not Intersect(Target, r) Is Nothing Then
For Each c In Intersect(Target, r).SpecialCells(xlCellTypeConstants, 3)
c.Value = UCase(c.Value)
Next c
End If
Application.EnableEvents = True
End Sub
============================
--ron
 
Back
Top