Capitalize stock symbol Data Entry

[ilcaa]

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

 

[Richard Schollar]

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:

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]

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]

Give this amended code a try:

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

 

[ilcaa]

works like a charm, thanks for the help!