UPPER function problem

[Ron Carr]

I have columns that the user enters x or nothing. Purely for aesthetics I
would like it to auto-capitalize, so I entered the function "=UPPER(+F5)"
where F5 is the current cell. What shows is excatly what I just entered: ie.,
the function! So there are two questions, why do I have the =UPPER etc
displaying in the cell and is it the correct use of the function to uppercase
the contect in the cell.
Excel 2007.
Many thanks!
Ron

 

[Mike H]

Hi,

the correct syntax is

=Upper(f5)

Mike

 

[Ron Carr]

Nope. tried it both ways, with and without the +.
Ron

 

[Mike H]

Hi,

2 possibilites.

1. Most likely. The cell is formatted as text. Reformat as general, tap F2
and Enter
2. You are set to display formulas instead of results. I know how to change
this in excel 2003 so you'll have to look in Help for 2007.

Mile

 

[Bernard Liengme]

You cannot use a formula to change a cell that has something in it.
If F5 has x then the formula =UPPER(F5) in some OTHER cell will display X

You seem to want the cell F5 where the user types an x to automatically
change to a capital X
This would require some VBA code. Are you ready to enter that area of Excel?

best wishes

 

[Ron Carr]

Turns out it is a circular reference since I am reffing to the same cell the
formula is in. I assume that is why the entire formula is showing.
Show formulas applies to the whole spreadsheet and other formulas are not
showing.
I still would like to know how to auto-capitalize this field!
Ron

 

[Bernard Liengme]

read my message

 

[Rick Rothstein]

You can't do what you want with formulas. Think about it for a moment... if
you have a formula in a cell and your user types something in that cell,
whatever they type will replace the formula. As Bernard pointed out, what
you want to do will require some VB coding. Give this a try. Right click the
tab at the bottom of the worksheet containing the columns you want to have
this functionality and select View Code from the popup menu that appears,
then copy/paste the following code into the code window that appeared...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim Text As String
Dim Cols() As String
Const UpperCaseColumns As String = "A,B,E,H"
Cols = Split(UpperCaseColumns, ",")
For X = 0 To UBound(Cols)
Cols(X) = Trim(Cols(X)) & ":" & Trim(Cols(X))
Next
Text = Join(Cols, ",")
If Not Intersect(Target, Range(Text)) Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
End If
Whoops:
Application.EnableEvents = True
End Sub

You didn't tell us which columns. As written, the code will upper case any
text entered into Columns A, B, E and H. If you want different columns to
have this functionality, just modify the list to in the Const
UpperCaseColumns statement to show your column letters in a comma delimited
listing as my sample shows. Once you have finished, any entries made to the
columns you specified will automatically be turned into upper case letters.

 

[Ron Carr]

OK I will try the code, just for kicks.
I have been writing a lot of VBA in Access but never tried in Excel etc..
Time to learn!
Thanks.
Ron

 

[Michael Bean]

Hello! This is exactly what I'm looking for as well! Thank you for
writing this code. However, I pasted it into the window as instructed,
and nothing happens in the Excel sheet. I understand how to limit the
columns, but I'm not sure how to execute the code... Please help!

*** Sent via Developersdex http://www.developersdex.com ***

 

[Gord Dibben]

Did you paste the code into the sheet module?

Do you have that sheet activated when entering text?

Code is executed when you type some text into any cell in the designated
column(s)


Gord Dibben MS Excel MVP

 

[Michael Bean]

Gord-

Thannks, I understand now; it works on new text, but not on old text. I
was expecting it to execute the code on pre-existing text as well. (I
followed the instructions and pasted as described, I don't know about
activating the sheet, but it is doing what it's meant to do.)

I work with a database which exports an Excel address list file, and the
text needs to be changed to all caps before printing the labels. I had
to manually retype each field. This code now allows me to click each
field then click the text and it becomes all caps. Now if it could be
automated! I considered writing my own app to do it from the desktop,
but it would be best if it could be done right in Excel. Unfortunately,
I don't know much about VBA.

Michael

*** Sent via Developersdex http://www.developersdex.com ***

 

[Gord Dibben]

We thought you wanted the text UPPERED as you typed it in but I see you have
a different need.

To change all pre-existing text to UPPER, run this macro on your selected
cells.

Sub Upper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = UCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub


Gord

 

[Michael Bean]

Gord-

Perfect! Thank you so much! I ran your code and my Excel sheet is in all
CAPS now!

In case other readers want a concise explanation, here's how I used the
code with the instructions from earlier in this post. I hope this is
correct:

-Copy the code shown in Gord's post.
-Select the cells you want to become all caps. DON'T USE CTRL-A!
-Right-click the "Sheet 1" tab (or whatever Sheet #) at the bottom of
the sheet and select View Code.
-Paste the text in the new window in the Visual Basic text edit window.
-Select Run > Run Sub/User Form from the menu.
-Go back to the Excel sheet and your selected cells should be all CAPS!

Just a note, DON'T SELECT ALL (CTRL-A), just select the cells that
contain your data. Select All ties up Excel for a looooooong time
because the code is run on every possible cell, and there are a lot of
cells.

PS. Ron Carr had asked about Upper Case as text is entered, and I think
he got his answer. Then *I* asked about Upper Case after text is entered
and saved. So both of us got our answer! Thank you so much!

*** Sent via Developersdex http://www.developersdex.com ***

 

[Gord Dibben]

Michael

Thanks for the feedback.

A revision to your instructions to other readers.

I would not put the Macro in a worksheet module............those are best
reserved for with event code which the first set of code was.

The code I posted is a manually run macro and should go into a General
module.

Alt F11 to open the VBE.

CTRL " r to open Project Explorer.

Select your project/workbook and Insert>Module.

Place the macro in that module.


Gord