How to make First letter of the cell in capital letter

[Irshad Alam]

I have a sheet in which there are text matters.

I want to run a code, so that all the cells of the worksheet can be effected
to change the first letter of the cell into capital.

I was able to know with the web helps examples, by that I can do it manually
with the cell reference one by one. the formula is as below :

=CONCATENATE(UPPER(LEFT(A1,1)),LOWER(RIGHT(A1,LEN(A1)-1)))

But I want to run in a code for the complete sheet, so once I run the code
and it can be effective for all the sheet

pls. advice

Irshad

 

[Bob Phillips]

Dim cell As Range

For Each cell In ActiveSheet.UsedRange

If cell.Value <> "" Then

cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value,
Len(cell.Value) - 1)
End If
Next cell

 

[Stefi]

Why not

cell.Value = WorksheetFunction.Proper(cell.Value)

Stefi


„Bob Phillips†ezt írta:

 

[Bob Phillips]

Partly because I would rather not call out to worksheet functions when
there is no need to, and partly because it doesn't do what the OP asks for.

 

[Roger Govier]

Hi Bob

Not sure in what way the Proper function doesn't achieve what the OP
requests.

 

[Stefi]

Hi Bob,

Please explain disadvantages of calling worksheet functions when also other
means are available.

What is the difference between the two ways of changing the first letter to
capitals?

Thanks,
Stefi


„Bob Phillips†ezt írta:

 

[Dave Peterson]

If The User Wants Every Word To Have Its First Letter Uppercase, Then Proper
Would Work.

But if the user wants just the first letter in the cell uppercase, then maybe
not.

But I'd have used:

cell.Value = UCase(Left(cell.Value, 1)) _
& lcase(Right(cell.Value, Len(cell.Value) - 1))

or

cell.Value = UCase(Left(cell.Value, 1)) & lcase(mid(cell.Value, 2)

to match the sample worksheet function the OP gave.

 

[Dave Peterson]

Calling worksheet functions instead of staying inside of VBA is usually
(always???) slower.

And there's a difference if there are are multiple words in the cell.

And as well as ucase() and lcase(), VBA has its own strconv(), too.

 

[JP]

To add to what Dave said, if you are going to use a VBA solution, the
less worksheet calls, the better, because VBA has to do some behind-
the-scenes work to actually interface with Excel. It's not the fastest
way to do things. You want to limit your worksheet calls as much as
possible (when appropriate) to the times when you are reading data, or
writing data back to the worksheet.

--JP

 

[Bob Phillips]

Because he/she asked for the first letter, not the first letter of every
word.

 

[Bob Phillips]

As has been mentioned, there is an overhead to calling a worksheetfunction,
as you are calling out to Excel. That means all of the appropriate
information is passed to Excel (via the inefficient COM inerface?) and then
Excel has to do the function (which is probably very quick), and then Excel
has to pass it back via the same channel. This process is costly, and whilst
acceptable for one or tow instances, in a loop it doesn't sound like a good
idea to me.

The OP also said that they wanted to change the first letter of the cell,
not the first letter of every word in the cell as Proper would do.

 

[Roger Govier]

Absolutely right, Bob. I hadn't thought of he cells possibly containing
multiple words.

 

[Irshad Alam]

Sir,
I have checked the VB code you advised(the above first reply), it is not
same what i require, it is making the all the capital to all the first word
of that cell-

The code you advised was :
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Value <> "" Then
cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value,
Len(cell.Value) - 1)
End If
Next cell

Please note that my requirement is to make only the first word first letter
capital only
Exactly the result which this formula gives

=CONCATENATE(UPPER(LEFT(A1,1)),LOWER(RIGHT(A1,LEN(A1)-1)))

My requirement is exactly the result of the above formula BUt for all the
sheet.

Regards

Irshad

 

[Stefi]

Thank all of you for the detailed explanation!
Stefi


„Bob Phillips†ezt írta:

 

[Bob Phillips]

Sorry, but it just doesn't do that, something else must be happening.

 

[Dave Peterson]

I'd use:
cell.Value = UCase(Left(cell.Value, 1)) & lcase(mid(cell.Value, 2)

 

[Irshad Alam]

sorry for late reply, was out.

I have re-checked my above posting works fine, the same type in Microsoft
word if you start a sentence by typing small letters, when you move to next
line pressing enter, it automatically makes the first letter of the sentence
capital.

Exactly this is my requirement which I am able to solve by the indiviual
cell by this formula:

=CONCATENATE(UPPER(LEFT(A1,1)),LOWER(RIGHT(A1,LEN(A1)-1)))

But I want a vba, so that once I run, it will work for the complete
selection for making all the cells first letter (of first word) into capital.

Regards

Irshad

 

[Gord Dibben]

You want it to occur when you hit ENTER key use this event code adapted from
Bob's code.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" 'adjust to suit
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If cell.Value <> "" Then
cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value, _
Len(cell.Value) - 1)
End If
Next cell
End If
ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module. Edit to suit the range.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP