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

[Shannonn]

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]

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

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