|  | Changing Case in a Cell | | |
| | | JimS |  |
|  Posted: Wed Aug 27, 2008 7:36 am Post subject: Changing Case in a Cell |  |
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 |
| |
| | | Ron Rosenfeld | |
| | Posted: Wed Aug 27, 2008 9:11 am Post subject: Re: Changing Case in a Cell | |
| |  | |
On Wed, 27 Aug 2008 02:36:51 -0700, JimS <jimx22@msn.com> wrote:
| Quote: | 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
|
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 |
| |
| | | JimS | |
| | Posted: Wed Aug 27, 2008 9:29 am Post subject: Re: Changing Case in a Cell | |
| | | |
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?
On Wed, 27 Aug 2008 07:11:16 -0400, Ron Rosenfeld
<ronrosenfeld@nospam.org> wrote:
| Quote: | On Wed, 27 Aug 2008 02:36:51 -0700, JimS <jimx22@msn.com> wrote:
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
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 |
|
| |
| | | Pete_UK | |
| | Posted: Wed Aug 27, 2008 10:22 am Post subject: Re: Changing Case in a Cell | |
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
On Aug 27, 10:36 am, JimS <jim...@msn.com> wrote:
| Quote: | 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 |
|
| |
| | | Gord Dibben | |
| | Posted: Wed Aug 27, 2008 11:10 am Post subject: Re: Changing Case in a Cell | |
| | | |
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
On Wed, 27 Aug 2008 04:29:15 -0700, JimS <jimx22@msn.com> wrote:
| Quote: | 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?
On Wed, 27 Aug 2008 07:11:16 -0400, Ron Rosenfeld
ronrosenfeld@nospam.org> wrote:
On Wed, 27 Aug 2008 02:36:51 -0700, JimS <jimx22@msn.com> wrote:
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
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 |
|
| |
| | | JimS | |
| | Posted: Wed Aug 27, 2008 11:53 am Post subject: Re: Changing Case in a Cell | |
No, I don't mind. I just made the "other" cell invisible. Thanks for
the formula.
On Wed, 27 Aug 2008 03:22:15 -0700 (PDT), Pete_UK
<pashurst@auditel.net> wrote:
| Quote: | 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
On Aug 27, 10:36 am, JimS <jim...@msn.com> wrote:
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 |
|
| |
| | | Pete_UK | |
| | Posted: Wed Aug 27, 2008 2:50 pm Post subject: Re: Changing Case in a Cell | |
You're welcome, Jim - thanks for feeding back.
Pete
On Aug 27, 2:53 pm, JimS <jim...@msn.com> wrote:
| Quote: | No, I don't mind. I just made the "other" cell invisible. Thanks for
the formula.
|
|
| |
| | | Ron Rosenfeld | |
| | Posted: Wed Aug 27, 2008 4:20 pm Post subject: Re: Changing Case in a Cell | |
On Wed, 27 Aug 2008 04:29:15 -0700, JimS <jimx22@msn.com> wrote:
| Quote: | 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?
|
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 |
| |
|
|
