Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using UCase
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using UCase
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 -- Gary Keramidas Excel 2003 "Randy P" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using UCase
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) "Randy P" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ucase / macros | Excel Discussion (Misc queries) | |||
Use of UCase | Excel Programming | |||
UCase? | Excel Programming | |||
UCase in textbox, help | Excel Programming | |||
UCase Help | Excel Programming |