|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
January 7th, 2008, 01:29 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Capital Letter in a cell
I have designed my worksheet by using format "Merge Cell" like
A B C D E F G H I J K 1 PAN NO. ABCDEF1234G TAN NO. VWXY012345Z From B1 to E1 & G1 to K1 is formated cell "Merge". I want whenever write in B1 or G1 it will be automatically Capital Letter. For this purpose I asked for help & got a VBA code from this forum & also have used to my worksheet (by pressing Alt+f11). The code is as follows - Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("b1:E1") r = Range("g1:k1") Application.EnableEvents = False If Application.WorksheetFunction.IsText(r) Then r.Value = UCase(r.Value) End If Application.EnableEvents = True End Sub But it's not working means it's not being automatic capital letter. Help me what should I do. 
|
|
#2
January 7th, 2008, 02:20 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Capital Letter in a cell
Montu,
Try it this way: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B1,G1")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Application.EnableEvents = False If Application.WorksheetFunction.IsText(Target.Value) Then Target.Value = UCase(Target.Value) End If Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Montu" wrote in message ... I have designed my worksheet by using format "Merge Cell" like A B C D E F G H I J K 1 PAN NO. ABCDEF1234G TAN NO. VWXY012345Z From B1 to E1 & G1 to K1 is formated cell "Merge". I want whenever write in B1 or G1 it will be automatically Capital Letter. For this purpose I asked for help & got a VBA code from this forum & also have used to my worksheet (by pressing Alt+f11). The code is as follows - Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("b1:E1") r = Range("g1:k1") Application.EnableEvents = False If Application.WorksheetFunction.IsText(r) Then r.Value = UCase(r.Value) End If Application.EnableEvents = True End Sub But it's not working means it's not being automatic capital letter. Help me what should I do.
|
|
#3
January 8th, 2008, 05:25 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Capital Letter in a cell
I have copy & past of this code to Excel Visual Basic Editor. But it's not
working. should I creat shortcut key ?. I want it will be automatically capital letter with in the range, How would be it possible. help me thanks in advance. "Bernie Deitrick" wrote: Montu, Try it this way: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B1,G1")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Application.EnableEvents = False If Application.WorksheetFunction.IsText(Target.Value) Then Target.Value = UCase(Target.Value) End If Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Montu" wrote in message ... I have designed my worksheet by using format "Merge Cell" like A B C D E F G H I J K 1 PAN NO. ABCDEF1234G TAN NO. VWXY012345Z From B1 to E1 & G1 to K1 is formated cell "Merge". I want whenever write in B1 or G1 it will be automatically Capital Letter. For this purpose I asked for help & got a VBA code from this forum & also have used to my worksheet (by pressing Alt+f11). The code is as follows - Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("b1:E1") r = Range("g1:k1") Application.EnableEvents = False If Application.WorksheetFunction.IsText(r) Then r.Value = UCase(r.Value) End If Application.EnableEvents = True End Sub But it's not working means it's not being automatic capital letter. Help me what should I do.
|
|
#4
January 8th, 2008, 02:12 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Auto Capital Letter in a cell
Montu,
Copy the code, right-click the sheet tab of interest, select "View Code" and paste the code into the window that appears. The code is an event, which does not go into a regular codemodule, but into the codemodule of the worksheet. HTH, Bernie MS Excel MVP "Montu" wrote in message ... I have copy & past of this code to Excel Visual Basic Editor. But it's not working. should I creat shortcut key ?. I want it will be automatically capital letter with in the range, How would be it possible. help me thanks in advance. "Bernie Deitrick" wrote: Montu, Try it this way: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("B1,G1")) Is Nothing Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Application.EnableEvents = False If Application.WorksheetFunction.IsText(Target.Value) Then Target.Value = UCase(Target.Value) End If Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Montu" wrote in message ... I have designed my worksheet by using format "Merge Cell" like A B C D E F G H I J K 1 PAN NO. ABCDEF1234G TAN NO. VWXY012345Z From B1 to E1 & G1 to K1 is formated cell "Merge". I want whenever write in B1 or G1 it will be automatically Capital Letter. For this purpose I asked for help & got a VBA code from this forum & also have used to my worksheet (by pressing Alt+f11). The code is as follows - Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("b1:E1") r = Range("g1:k1") Application.EnableEvents = False If Application.WorksheetFunction.IsText(r) Then r.Value = UCase(r.Value) End If Application.EnableEvents = True End Sub But it's not working means it's not being automatic capital letter. Help me what should I do.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
