Capitalize stock symbol Data Entry

ilcaa

Well-known Member
Joined
May 25, 2005
Messages
686
I have and order entry spreadsheet for trading that in cell A1 goes a stock symbol..ex yhoo

I want it to automatically capitalize upon entry so yhoo becomes YHOO but without hitting a macro command

i tried this with no success,

Public Property Get UpperA1() As Variant
Range("a1").Formula = Upper("a1")
End Property

still does not capitalize the entry in A1, any help with this?

thanks. M
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

You'll probably be best off using a worksheet event - say you are only interested in what is entered in the A column (ie only the A column MUST be capitalised), then you could place the following in the sheet's Worksheet module:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A:A"), Target) Is Nothing Then
    Target.Value = UCase(Target.Value)
End If
End Sub

Best regards

Richard
 

ilcaa

Well-known Member
Joined
May 25, 2005
Messages
686
works great but... i have a command button that resets the worksheet

(basically deletes all the entries so I can use it again with another symbol)

and when i hit that button and I get a debug error message on line
Target.Value = UCase(Target.Value)

anyway to reset the worksheet properly so when i use it again the Ucase command works over and over without the debug error?

thanks for the help
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Give this amended code a try:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A:A"), Target) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
    Target.Value = UCase(Target.Value)
End If
End Sub

Richard
 

Watch MrExcel Video

Forum statistics

Threads
1,114,384
Messages
5,547,638
Members
410,805
Latest member
Ginoji
Top