Capitals

[Hellhole]

Im sure the answer to this is staring me in the face but how do I get lowercase letters typed into a form to turn into capitals. ie if I input n, it should change to N. There is only one letter per cell. Thanks for any help. PS have had a look at the UPPER command but could not get it to work for me!!

HELP PLEASE

[Tom Urtis]

The formula for that would be (as an example, in B1)
=UPPER(A1)
where A1 holds the one letter value that is currently showing in lower case.

Did you need something more than that?

[Mark O'Brien]

OK, you're going to have to use VBA for this. (I could be wrong, but this is the way I would do it)

for this example I'm using cells A1 and B1 on as the cells that contain the text.

Right click on the tab of the sheet that you will have the text on, and choose "View Code".

Copy and paste this code into the editor:

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Count > 1 Then Exit Sub

  If Target = [A1] _

  Or Target = [B1] Then

    Target.Value = UCase(Target.Value)

  End If

End Sub

You can change this code by changing A1 and B1 to be whatever cells you want to capitalise.

I hope this makes sense.

[Hellhole]

If the lowercase letter is entered in say A1, the uppercase letter also has to be in A1. The function you quoted takes the lowercase from one cell and puts the Capital in another cell.
I thought there might be a cell format option that I couldn't find that would be possible to use. There is something similar in Word that can be used when working with tables.

Any other idea's?

[Ivan F Moala]

On 2002-03-15 21:10, Hellhole wrote:
If the lowercase letter is entered in say A1, the uppercase letter also has to be in A1. The function you quoted takes the lowercase from one cell and puts the Capital in another cell.
I thought there might be a cell format option that I couldn't find that would be possible to use. There is something similar in Word that can be used when working with tables.

Any other idea's?

Hi hellhole
The procedure Mark gave you is A
Event procedure. It LOOKS @ A1 & B1....so that if you type in any letter it converts it to Uppercase.
If you use this then you will have to change
the range you need to monitor to get uppercases into the cells.


Post if still not clear

Ivan

[Anne Troy]

Just another, similar one...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub

To get all entries in column A through G to turn uppercase regardless of entry, change the range to "A:G"

[Hellhole]

On 2002-03-15 22:18, Dreamboat wrote:
Just another, similar one...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub

To get all entries in column A through G to turn uppercase regardless of entry, change the range to "A:G"

Great stuff. Thanks for your help everyone.
Not as somple as I thought it would be (ie a format option) but this works just great. So thanks once again.

Hellhole - London UK

[Anne Troy]

Well, okay, I admit. I actually stole it from:

http://www.cpearson.com/excel/case.htm

I like to give credit where credit is due.