Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Have user input converted to uppercase in same cell as input?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to mark down the time when entering input to a cell? | Excel Discussion (Misc queries) | |||
user input decides cell copy range | Excel Discussion (Misc queries) | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Cell Formula or Cell Input | Excel Worksheet Functions | |||
user input question | Excel Discussion (Misc queries) |