Auto Capital Letter in a cell

[Montu]

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.

 

[Bernie Deitrick]

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]

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]

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