Auto capital letter in a cell
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 |
Auto capital letter in a cell
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 |
Auto capital letter in a cell
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 |
Auto capital letter in a cell
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 |
All times are GMT +1. The time now is 04:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com