Force Upper Case

  • Thread starter Thread starter J Stand
  • Start date Start date
J

J Stand

Hi,

Does anyone know how to automatically display UPPERCASE even if lower case
is typed in a cell please?

Peter.
 
Dnia Mon, 19 Jul 2004 23:01:03 +0100, J Stand drêczony bólem
angelologicznym zarycza³ rozpaczliwie :
Hi,

Does anyone know how to automatically display UPPERCASE even if lower case
is typed in a cell please?

Peter.
Click to expand...



This is slightly modified procedure from the thread "User enters number
and 3 zeros are added at end of number"

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target.Value) Or Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Target.Value = VBA.UCase(Target.Value)
Application.EnableEvents = True
End Sub
 
this is the way Chip Pearson did it:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub
 
One way:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Not Intersect(.Cells, Range("A1,J10")) Is Nothing Then
If IsEmpty(.Value) Or .Count > 1 Then Exit Sub
Application.EnableEvents = False
.Value = VBA.UCase(.Text)
Application.EnableEvents = True
End If
End With
End Sub
 
No - UCase is a VBA Method so VBA.UCase and UCase are evaluated
identically. The original code had it in, so I just left it in.
 
Tue, 20 Jul 2004 22:28:09 +0100, J Stand wrote :
Thanks, how to I limit the process to certain cells?
Click to expand...

As mentioned before you can use

Application.Intersect(Target, Range)

or you can use Target.Row and Target.Column properties


Private Sub Worksheet_Change(ByVal Target As Range)

Dim RowMin As Integer
Dim RowMax As Integer
Dim ClMin As Integer
Dim ClMax As Integer

RowMin = 1
RowMax = 3
ClMin = 1
ClMax = 4

If IsEmpty(Target.Value) Or Target.Count > 1 Then Exit Sub
If (Target.Row >= RowMin) And (Target.Row <= RowMax) _
And (Target.Column >= ClMin) And (Target.Column <= ClMax) Then
Application.EnableEvents = False
Target.Value = VBA.UCase(Target.Value)
Application.EnableEvents = True
End If
End Sub

As you can see this procedure requires bit more coding , but IMHO it's
easier to understand
 
You're a genius, thank you very much.

mg said:
Tue, 20 Jul 2004 22:28:09 +0100, J Stand wrote :


As mentioned before you can use

Application.Intersect(Target, Range)

or you can use Target.Row and Target.Column properties


Private Sub Worksheet_Change(ByVal Target As Range)

Dim RowMin As Integer
Dim RowMax As Integer
Dim ClMin As Integer
Dim ClMax As Integer

RowMin = 1
RowMax = 3
ClMin = 1
ClMax = 4

If IsEmpty(Target.Value) Or Target.Count > 1 Then Exit Sub
If (Target.Row >= RowMin) And (Target.Row <= RowMax) _
And (Target.Column >= ClMin) And (Target.Column <= ClMax) Then
Application.EnableEvents = False
Target.Value = VBA.UCase(Target.Value)
Application.EnableEvents = True
End If
End Sub

As you can see this procedure requires bit more coding , but IMHO it's
easier to understand
Click to expand...
 
Back
Top