PC Review


Reply
Thread Tools Rate Thread

Format Cell - Only Upper Case Alpha characters

 
 
LinLin
Guest
Posts: n/a
 
      20th Mar 2009
Hi Everyone

Is there anyway to format a cell so that if data is entered as a lower case
alpha character, it will automatically change it to Upper case?

IE: I enter a

And excel comes back with A

Also for a combination of letters:

help
comes back as: HELP

I am not so concerned with a mix of lower and upper case (of course, if the
solution can ensure a mix becomes all upper case, that would be cool too!)

thanks!

 
Reply With Quote
 
 
 
 
CVinje
Guest
Posts: n/a
 
      20th Mar 2009
Here's an answer I found that worked when I tested it - taken from:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=70

Hope it works for you!!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

'Change A1:A10 to the range you desire
'Change UCase to LCase to provide for lowercase instead of uppercase

If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub


How to use:

Copy the code above.
Open a workbook.
Right-click the worksheet on which you'd like this code to operate, and hit
View Code.
Paste the code into the code window that appears at right.
Change the range A1:A10 in the code to the range suitable for your file.
Save the file, and close the Visual Basic Editor window.


Test the code:

Type any text into the range of cells you chose in your code.

CVinje

"LinLin" wrote:

> Hi Everyone
>
> Is there anyway to format a cell so that if data is entered as a lower case
> alpha character, it will automatically change it to Upper case?
>
> IE: I enter a
>
> And excel comes back with A
>
> Also for a combination of letters:
>
> help
> comes back as: HELP
>
> I am not so concerned with a mix of lower and upper case (of course, if the
> solution can ensure a mix becomes all upper case, that would be cool too!)
>
> thanks!
>

 
Reply With Quote
 
 
 
 
CVinje
Guest
Posts: n/a
 
      20th Mar 2009
Something that I didn't mention - when you use a code and it takes an action
on the sheet / workbook (i.e. - make everything capitalized), you loose the
ability to Undo. Just FYI.

"CVinje" wrote:

> Here's an answer I found that worked when I tested it - taken from:
> http://www.vbaexpress.com/kb/getarticle.php?kb_id=70
>
> Hope it works for you!!
>
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Application.EnableEvents = False
>
> 'Change A1:A10 to the range you desire
> 'Change UCase to LCase to provide for lowercase instead of uppercase
>
> If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
> Target(1).Value = UCase(Target(1).Value)
> End If
> Application.EnableEvents = True
> End Sub
>
>
> How to use:
>
> Copy the code above.
> Open a workbook.
> Right-click the worksheet on which you'd like this code to operate, and hit
> View Code.
> Paste the code into the code window that appears at right.
> Change the range A1:A10 in the code to the range suitable for your file.
> Save the file, and close the Visual Basic Editor window.
>
>
> Test the code:
>
> Type any text into the range of cells you chose in your code.
>
> CVinje
>
> "LinLin" wrote:
>
> > Hi Everyone
> >
> > Is there anyway to format a cell so that if data is entered as a lower case
> > alpha character, it will automatically change it to Upper case?
> >
> > IE: I enter a
> >
> > And excel comes back with A
> >
> > Also for a combination of letters:
> >
> > help
> > comes back as: HELP
> >
> > I am not so concerned with a mix of lower and upper case (of course, if the
> > solution can ensure a mix becomes all upper case, that would be cool too!)
> >
> > thanks!
> >

 
Reply With Quote
 
Chip Pearson
Guest
Posts: n/a
 
      20th Mar 2009
I would be careful with that. Add some code to ensure that you are not
overwriting a formula:

> Target(1).Value = UCase(Target(1).Value)


should be

If Target(1).HasFormula = False Then
Target(1).Value = UCase(Target(1).Value)
End If

Just to be complete, you might also want to ensure you're not working
in an array:

If Target(1).HasFormula = False Then
If Target(1).HasArray = False Then
Target(1).Value = UCase(Target(1).Value)
End If
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Thu, 19 Mar 2009 22:50:01 -0700, CVinje
<(E-Mail Removed)> wrote:

>Here's an answer I found that worked when I tested it - taken from:
>http://www.vbaexpress.com/kb/getarticle.php?kb_id=70
>
>Hope it works for you!!
>
>Option Explicit
>
>Private Sub Worksheet_Change(ByVal Target As Range)
>
> Application.EnableEvents = False
>
> 'Change A1:A10 to the range you desire
> 'Change UCase to LCase to provide for lowercase instead of uppercase
>
> If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
> Target(1).Value = UCase(Target(1).Value)
> End If
> Application.EnableEvents = True
>End Sub
>
>
>How to use:
>
>Copy the code above.
>Open a workbook.
>Right-click the worksheet on which you'd like this code to operate, and hit
>View Code.
>Paste the code into the code window that appears at right.
>Change the range A1:A10 in the code to the range suitable for your file.
>Save the file, and close the Visual Basic Editor window.
>
>
>Test the code:
>
>Type any text into the range of cells you chose in your code.
>
>CVinje
>
>"LinLin" wrote:
>
>> Hi Everyone
>>
>> Is there anyway to format a cell so that if data is entered as a lower case
>> alpha character, it will automatically change it to Upper case?
>>
>> IE: I enter a
>>
>> And excel comes back with A
>>
>> Also for a combination of letters:
>>
>> help
>> comes back as: HELP
>>
>> I am not so concerned with a mix of lower and upper case (of course, if the
>> solution can ensure a mix becomes all upper case, that would be cool too!)
>>
>> thanks!
>>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing upper case characters to upper/lower Richard Zignego Microsoft Excel Misc 1 17th Dec 2007 11:09 PM
Changing file in all upper case to upper and lower case =?Utf-8?B?U2FnaXQ=?= Microsoft Excel Misc 15 30th May 2007 06:08 AM
convert upper case text to lower case with the first letter upper =?Utf-8?B?Skg=?= Microsoft Access Macros 1 20th Aug 2006 09:07 PM
How do I convert lower-case characters to upper-case characters i. =?Utf-8?B?RCBCIEhlbG1z?= Microsoft Access Queries 1 20th Dec 2004 09:06 PM
All UPPER CASE to Upper & Lower Case =?Utf-8?B?ajk=?= Microsoft Excel Worksheet Functions 3 8th May 2004 09:04 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:58 AM.