Excel

Change Case to Upper Case on Data Entry

Ease of Use

Easy

Version tested with

2000 

Submitted by:

Anne Troy

Description:

Changes the case of text to upper case as soon as you type it and exit the cell. 

Discussion:

Use this code to have your part numbers, state abbreviations, and other data properly entered as all upper case or all lower case, as the case may be. This code runs on a worksheet change event, and so must be stored in the file and worksheet in which you will use it. 

Code:

instructions for use

			

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:

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

Test the code:

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

Sample File:

changecase.zip 7.3KB 

Approved by mdmackillop


This entry has been viewed 207 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express