Uppercase letters in alphanumeric entry

[l1011driver]

Thanks to anyone who may be able to help.

I have two columns in a worksheet that require alphanumeric entries. The columns in which the alphanumeric data are entered are column A and column B.

Would it be possible to write a VBA code that would change the lowercase alpha characters to uppercase upon exiting the cell? For example:

Entry into Cell A3: abc123
Output: ABC123

Other columns require lowercase entries so just using the caps lock key isn't terribly convenient.

Thanks to anyone who can help.

Sincerely,

Mark Barnard

 

[Rick Rothstein]

Give this event code procedure a try...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  Application.EnableEvents = False
  On Error Resume Next
  For Each Cell In Intersect(Target, Columns("A:B"))
    Cell.Value = UCase(Cell.Value)
  Next
  On Error GoTo 0
  Application.EnableEvents = True
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[l1011driver]

Hi Mr. Rothstein,

Worked like a champ. Thanks so much!

Mark Barnard

 

[l1011driver]

Hi Mr. Rothstein,

Worked like a champ. Thanks so much!

Mark Barnard

Mr. Rothstein,

Two more questions:

1. Is it possible to limit the alphanumeric code you sent me such that the code modifies the cells in columns A and B starting with cells A3 and B3? I have headers in cells A1, A2, B1 and B2 that I’d like to leave unmodified by your code.

2. Is it possible to write the code such that it would also apply to my column J? Columns C through I would not be affected as I’d still like to use some lowercase letters in those columns. In column J, on the other hand, I would like to have uppercase alphabetic characters.

Thank you for any help you might be.

Mark Barnard

 

[Rick Rothstein]

Two more questions:

1. Is it possible to limit the alphanumeric code you sent me such that the code modifies the cells in columns A and B starting with cells A3 and B3? I have headers in cells A1, A2, B1 and B2 that I’d like to leave unmodified by your code.

2. Is it possible to write the code such that it would also apply to my column J? Columns C through I would not be affected as I’d still like to use some lowercase letters in those columns. In column J, on the other hand, I would like to have uppercase alphabetic characters.

Replace the code I gave you earlier with the following...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  Application.EnableEvents = False
  On Error Resume Next
  For Each Cell In Intersect(Target, Intersect(ActiveSheet.UsedRange, Range("A3:B" & Rows.Count & ",J3:J" & Rows.Count)))
    Cell.Value = UCase(Cell.Value)
  Next
  On Error GoTo 0
  Application.EnableEvents = True
End Sub[/td]
[/tr]
[/table]

 

[l1011driver]

Replace the code I gave you earlier with the following...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  Application.EnableEvents = False
  On Error Resume Next
  For Each Cell In Intersect(Target, Intersect(ActiveSheet.UsedRange, Range("A3:B" & Rows.Count & ",J3:J" & Rows.Count)))
    Cell.Value = UCase(Cell.Value)
  Next
  On Error GoTo 0
  Application.EnableEvents = True
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Mr. Rothstein,

Works perfectly. You're amazing.

Thanks so much!

Mark Barnard