Convert to uppercase

  • Thread starter Thread starter TheMilkGuy
  • Start date Start date
T

TheMilkGuy

Hi folks,

I'm trying to force a couple of ranges into uppercase. These cells
are mirrors of input from other pages. I want the users to feel free
to use upper or lower case, but have the end result in upper.

Here is the formula I was using:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("Z2:AI2,Z3,Z4")) Is
Nothing) Then
With Target
If Not .HasFormula Then
.Value = UCase(.Value)
End If
End With
End If
End Sub

But it doesn't work, I suppose since these cells are full of formulae
like "=InputI19" and whatnot.

I tried changing the script but all I did was flatten the formula-
holding cells into straight text and then made that text uppercase.

Suggestions appreciated.

Cheers,
Craig
 
The alpha character case for a cell with a formula is determined by the formula;
and there is no upper/lower case for numeric values, only font.name, font size and font.bold.

For those cells without formulas then perhaps your code is not specific enough.
The "Target" is whatever was selected by the user and that could be multiple cells.
The conventional method is to use "Target(1)" to avoid selection problems or to force user compliance...
'--
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target(1), _
Range("Z2:AI2,Z3,Z4")) Is Nothing) Then
If Not Target(1).HasFormula Then
Target(1).Value = UCase(Target(1).Value)
End If
End If
End Sub
'--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
(Special Sort Excel add-in)

..
..
..

"TheMilkGuy" <[email protected]>
wrote in message
Hi folks,

I'm trying to force a couple of ranges into uppercase. These cells
are mirrors of input from other pages. I want the users to feel free
to use upper or lower case, but have the end result in upper.

Here is the formula I was using:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("Z2:AI2,Z3,Z4")) Is Nothing) Then
With Target
If Not .HasFormula Then
.Value = UCase(.Value)
End If
End With
End If
End Sub

But it doesn't work, I suppose since these cells are full of formulae
like "=InputI19" and whatnot.

I tried changing the script but all I did was flatten the formula-
holding cells into straight text and then made that text uppercase.

Suggestions appreciated.

Cheers,
Craig
 
Back
Top