Auto capital letter in a cell

[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

 

[T. Valko]

Change this line:

Set r = C5:F5

Change to:

Set r = Range("C5")

 

[Gord Dibben]

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

 

[Gord Dibben]

Jeez

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

See Biff's reply.


Gord