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

Change this line:

Set r = C5:F5

Change to:

Set r = Range("C5")

--
Biff
Microsoft Excel MVP


"Montu" wrote in message
...
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

On Fri, 21 Dec 2007 22:05:01 -0800, Montu wrote:

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

Jeez

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

See Biff's reply.


Gord

On Sat, 22 Dec 2007 15:18:55 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Fri, 21 Dec 2007 22:05:01 -0800, Montu wrote:

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