Have user input converted to uppercase in same cell as input?

Hi all
Noob Alert! Very new to spreadsheets! I have a simple form that requires
data selected from a text description to be entered in a cell to give us a
special product code.

The code is upper case in the description but ppl are entering it in lower
case, I need to convert the entry to uppercase to match our entry system, but
I need a function to convert and display the converted text in the original
cell.

I looked at =UPPER function but that displays converted text elsewhere, if I
use it in the same cell as input I want converted I get the circular error
msg.

Can someone perhaps take time to show me how to make a drop down selection
where I can control the selection output please? That would look better and
solve the dilemma.

Thanks in advance
ShannonN

Shannon

This would require the use of VBA coding.

Either event code to change the text to UPPER as it is entered or a macro to
change existing text to UPPER.

Event code................

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 8 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

As written, operates on Columns A:H as you enter text in a cell.

Change the 8 to whatever you wish.

This is event code and must go into the sheet module.

Right-click on the sheet tab and "View Code". Copy and paste the above into
that module.

Macro.....................

Sub Upper_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = UCase(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo


Gord Dibben MS Excel MVP
On Mon, 19 Jun 2006 18:20:01 -0700, Shannonn
<[email protected]> wrote:

>Hi all
>Noob Alert! Very new to spreadsheets! I have a simple form that requires
>data selected from a text description to be entered in a cell to give us a
>special product code.
>
>The code is upper case in the description but ppl are entering it in lower
>case, I need to convert the entry to uppercase to match our entry system, but
>I need a function to convert and display the converted text in the original
>cell.
>
>I looked at =UPPER function but that displays converted text elsewhere, if I
>use it in the same cell as input I want converted I get the circular error
>msg.
>
>Can someone perhaps take time to show me how to make a drop down selection
>where I can control the selection output please? That would look better and
>solve the dilemma.
>
>Thanks in advance
>ShannonN

Gord Dibben MS Excel MVP