Forcing All Caps in input cells

[Slave2Six]

I have a data entry spreadsheet that will be used by multiple employees.
For ease of viewing later, I want to ensure that all data entered into
the input cells are in all caps.

Is there a way to force cells to display all caps at the time of input?


If not, is there a way in VBA that I can create a macro that will
change the case of the contents of these cell?

 

[Ken Johnson]

Is there a way to force cells to display all caps at the time of input?

Hi Slave2Six,

This worksheet Change event procedure automatically changes inputs of
the range A1:A10 to capitals. Just edit the "$A$1:$A$10" part to suit
your needs...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, _
Range("$A$1:$A$10")) Is Nothing Then
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
End If
Application.EnableEvents = True
End Sub

to get the code in place...

1. Copy it

2. Right click your sheet's sheet tab then select "View Code" from the
popup menu

3. Paste the code into the code module that appears

4. Press Alt + F11 to get back to the usual Excel interface

5. If this is your workbook's first macro and if the security level is
not set on medium then go Tools|Macro|Security...select
Medium|Save|Close|Open...Click on "Enable Macros" on the "Security
Warning" dialog that appears each time you open the workbook.

Ken Johnson

 

[Slave2Six]

Ken,

Thanks so much! That's exactly what I needed.

-Slave

 

[Slave2Six]

Ken,

The addition of this code has introduced a new challenge. In the
attached document, there is a VLOOKUP function that references the
"Product ID" columns. I used Range("$A$1:$EA$178") in the script that
you gave me. However, if I delete anything out of the Product ID
columns I now get a runtime error.

In reality, the only columns that I am concerned about are B, BC, and
DD.

Any sugestions?

 

[Ken Johnson]

Ken,

The addition of this code has introduced a new challenge. In the
attached document, there is a VLOOKUP function that references the
"Product ID" columns. I used Range("$A$1:$EA$178") in the script that
you gave me. However, if I delete anything out of the Product ID
columns I now get a runtime error.

In reality, the only columns that I am concerned about are B, BC, and
DD.

Any sugestions?

Hi Slave2Six,

I thinks its a harmless error caused by you selecting then deleting a
range of more than 1 cell, Excel can't work with the values of more
than one cell at a time.

If I'm right, then the harmless error will be ignored, and no error
message will be displayed when you delete multiple cell values, after
you change to the following code...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Application.Intersect(Target, _
Range("$A$1:$A$10")) Is Nothing Then
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
End If
Application.EnableEvents = True
End Sub


You will notice the only difference is the new first line..

On Error Resume Next

which you could easily type into the original code.

Let me know if that is the solution.

Ken Johnson

 

[Ken Johnson]

Hi Slave2Six,

I forgot to add..

the error may have resulted in Events being Disabled. If this is the
case then the code will stop working until Events are Enabled.

The simplest way to Enable Events is to Close the workbook after
saving, then reopen. Events are always enabled upon opening the
workbook.

Ken Johnson

 

[Gord Dibben]

Looks like Ken has you all fixed up.

Just a note here: ALL CAPS is definitely not easy to view.

Quite the opposite.


Gord Dibben MS Excel MVP

 

[Slave2Six]

Thanks again Ken. I'll give it a shot.

 

[Ken Johnson]

You're welcome Slave2Six.
Thanks for the feedback.
Ken Johnson