Forced Capitals

  • Thread starter Thread starter ianripping
  • Start date Start date
I

ianripping

I have this formula for forcing capitals to be entered into column C:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Column = 3 Then
For Each cell In Target.Cells
With cell
If Not .HasFormula And Not IsNumeric(.Value) Then
Application.EnableEvents = False
..Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
Next cell
End If
End Sub

Could anybody help me configure it so I can do this for Column D and E
as well? (I.E. Column's 4 and 5)
 
Hi Ian!

Try:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Column >= 3 And Target.Column <= 5 Then
For Each cell In Target.Cells
With cell
If Not .HasFormula And Not IsNumeric(.Value) Then
Application.EnableEvents = False
Target.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
Next cell
End If
End Sub

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Ian,

Replace your statement :
If Target.Column = 3 Then
with :

If Target.Column > 3 And Target.Column < 6 Then

Your routine as it is now doesn't replace the input with the capital.
To accomplish you should also replace :
Value = UCase(.Value)
with :
..Value = UCase(.Value)
( NB the . before the first Value)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Sorry >3 should be >2

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

A.W.J. Ales said:
Ian,

Replace your statement :
If Target.Column = 3 Then
with :

If Target.Column > 3 And Target.Column < 6 Then

Your routine as it is now doesn't replace the input with the capital.
To accomplish you should also replace :
Value = UCase(.Value)
with :
.Value = UCase(.Value)
( NB the . before the first Value)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
Click to expand...
 
Back
Top