First Letter Each Word UpperCase- Both for Cells & TextB

I_AM

Board Regular
Joined
Jul 2, 2002
Messages
139
Hi,
How to format so that the first letter of each word entered into a cell (and Also for a TextBox) is UpperCase and the rest of the word is LowerCase?

Thanks Regards Ron
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

I_AM

Board Regular
Joined
Jul 2, 2002
Messages
139
Re: First Letter Each Word UpperCase- Both for Cells & T

Hi
Thanks for that. In your example I wish to type "me" in Cell("A1") and have then Cell(A1") display "Me" with out referencing other cells. Is this possable? Also hopeing for similer with a TextBox via VBA.

Thanks regards Ron
 

I_AM

Board Regular
Joined
Jul 2, 2002
Messages
139
Re: First Letter Each Word UpperCase- Both for Cells & T

Hi
Thanks for that. In your example I wish to type "me" in Cell("A1") and have then Cell(A1") display "Me" with out referencing other cells. Is this possable? Also hopeing for similer with a TextBox via VBA.

Thanks regards Ron
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883

ADVERTISEMENT

hi!
if you wan to type in "me" in A1 and have it become "Me", then i dont know of any formula. but in VBA yes.
try this.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    Target.Value = Application.WorksheetFunction.Proper(Target.Value)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Re: First Letter Each Word UpperCase- Both for Cells & T

Hi I_Am:

You may want to try ...
Code:
activecell=StrConv(activecell,vbProperCase)
 

I_AM

Board Regular
Joined
Jul 2, 2002
Messages
139

ADVERTISEMENT

Re: First Letter Each Word UpperCase- Both for Cells & T

Thanks once again,
That certainly works, however it stops some of my other code from working giving an "error 13 Type mismatch". Also if i try to highlight an entire Row I get the same error?
Regards Ron
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
ok!
I understand that it will give you that erro if you highlight multiple cells. this will solve that.
Private Sub Worksheet_Change(ByVal Target As Range)
for each c in target
c.Value = Application.WorksheetFunction.Proper
(c.Value)
next c
End Sub

but in regards to your other code, you may try to make calcualtions that are not compatible such as multiplying text and a number, etc.
show your code that gives error.
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
hi!
i think i miss something.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Target
        c.Value = Application.WorksheetFunction.Proper(c.Value)
    <SPAN style="color:#00007F">Next</SPAN> c
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

I_AM

Board Regular
Joined
Jul 2, 2002
Messages
139
Re: First Letter Each Word UpperCase- Both for Cells & T

Thank you both,
Much appreciated! Yogi gets the prize on this one as it slots in to my code easily. Thanks again Regards ron
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top