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 InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.
Run the macro by going to ToolMacroMacros.
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
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