Format Cell - Only Upper Case Alpha characters
 

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!

Format Cell - Only Upper Case Alpha characters
 

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!

Format Cell - Only Upper Case Alpha characters
 

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!

Format Cell - Only Upper Case Alpha characters
 

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
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!