Capitals

Hellhole

New Member
Joined
Mar 14, 2002
Messages
3
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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?
 
Upvote 0
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:

<pre>
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</pre>

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

I hope this makes sense.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top