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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,128
Messages
5,857,531
Members
431,884
Latest member
Gcmoore63

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
Top