Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ucase / macros
hi, trying to get ucase to work, would settle for the 1 liner that works..
just to get the command to work, but don't see the answer anywhere that combines a couple things (if have wording right): - event macro, mixed in with other items, or - reference to specific cells, columns - similar, many different cells or multiple columns, whether: - ucase, or lcase, thanks in advance closest script have is for separate sheet entry guesse, but not working. have: (can I get rid of private sub & option explicit have....) Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myStr As String With Target If .Cells.Count 1 Then Exit Sub If .HasFormula Then Exit Sub If Intersect(.Cells, Me.Range("A:a")) Is Nothing Then Exit Sub On Error GoTo ErrHandler: 'If Not Intersect(Me.Range("A:A"), .Cells) Is Nothing Then myStr = UCase(myStr) Application.EnableEvents = False .Value = myStr End If End With ErrHandler: Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ucase / macros
found this that seems to work, modified so formula's not affected.
question: how to enter multiple ranges, columns & separate cell targets. Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Cells.Count 1 Then Exit Sub If .HasFormula Then Exit Sub If Not Intersect(Me.Range("A:A"), .Cells) Is Nothing Then 'If Not Application.Intersect(Range("a1:a20"), Target) Is Nothing Then On Error GoTo ErrHandler Application.EnableEvents = False Target.Formula = UCase(Target.Formula) End If End With ErrHandler: Application.EnableEvents = True End Sub (reference: Subject: convert lower to upper case automatically without using UPPER, gord.. 10/31/2008 9:19 AM PST) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros: can you copy macros from one doc to another? | Excel Discussion (Misc queries) | |||
Excel 2007 macros - how to merge 5 macros together into one | Excel Discussion (Misc queries) | |||
Macros warning always shows up, even if all macros removed | Excel Discussion (Misc queries) | |||
Macros - copying macros from one computer to another | Excel Discussion (Misc queries) | |||
Training: More on how to use macros in Excel: Recording Macros | Excel Worksheet Functions |