Uppercase letters in alphanumeric entry

l1011driver

New Member
Joined
Dec 26, 2014
Messages
48
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,507
Office Version
2010
Platform
Windows
Give this event code procedure a try...
Code:
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

New Member
Joined
Dec 26, 2014
Messages
48
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,507
Office Version
2010
Platform
Windows
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...
Code:
[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

New Member
Joined
Dec 26, 2014
Messages
48
Replace the code I gave you earlier with the following...
Code:
[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
 

Forum statistics

Threads
1,082,258
Messages
5,364,081
Members
400,778
Latest member
Canadian Sal

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top