using UCase

[Randy P]

I want to have my input in column 2 to be all uppercase. It may include a
number or 1-5 letters in lowercase. I tried the solution offered by Rick
Rothstein to Michael Koerner, but when I cut & pasted that code into my code,
I get a request to choose one of my existing macros. The macros drop down
box appears.

Here is my macro code:

Sub Sort_A_to_Z(ByVal Target As Range)

If Target.Column = 2 Then
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
Application.EnableEvents = True
End If

ActiveWorkbook.Worksheets("Dividend Calc").ListObjects("Table14").Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Dividend Calc").ListObjects("Table14").Sort. _
SortFields.Add Key:=Range("Table14[[#All],[Stock Sym]]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Dividend
Calc").ListObjects("Table14").Sort
.Header = xlYes
' .MatchCase = False
.Orientation = xlTopToBottom
' .SortMethod = xlPinYin
.Apply
End With

Range("b1").Select
End Sub

If I insert this code instead of the "target column" code, I get all caps,
but if I have more than a single alpha character, it truncates to a single
character, rather than the 1-5 characters I might have entered.

For count = 0 To 23
Range("b3").Offset(count, 0) = UCase(Range("c3").Offset(count, 0))
Next count

I'm confused.

 

[Gary Keramidas]

see if this does what you need.

open the vb editor, double click the sheet you want this to work on under
microsoft excel objects and paste this code. it should only work in column B
and only if 1 cell is selected.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Count = 1 Then
Target.Value = UCase(Target.Value)
End If
End Sub

 

[Patrick Molloy]

use Gary's suggestion. FYI parameterized SUBs (or Macros) don't appear in
the run macros list as its not possible to assign them to objects( - well
it is possible, but its tricky)