Auto capital letter in a cell

  • Thread starter Thread starter Montu
  • Start date Start date
M

Montu

C5:F5 is merge cell of sheet 1, I want whenever wright somthing on the merge
cell it will be automatically capital letter without using caps lock button,
For this reason I wrote for help & got a VBA code from this forum. Then I
copy, past & change the range in my VBA Editor follows -
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = C5:F5
Application.EnableEvents = False
If Application.WorksheetFunction.IsText(r) Then
r.Value = UCase(r.Value)
End If
Application.EnableEvents = True
End Sub
but its not working, What should I do. Help me
 
You have to iterate through the cells in the range r

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Me.Range("C5:F5")
On Error GoTo stoppit
Application.EnableEvents = False
For Each cell In r
cell.Value = UCase(cell.Value)
Next cell
stoppit:
Application.EnableEvents = True
End Sub

Or do it this way without iteration..........

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C5:F5" '<=== change to suit
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = UCase(.Value)
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Note: allways best to trap for errors and turn events back on if an error is
encountered.


Gord Dibben MS Excel MVP
 
Jeez

Missed the part about merged cells......my brain won't even let the words
"merged cells" register<g>

See Biff's reply.


Gord
 
Back
Top