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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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
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
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
 

Forum statistics

Threads
1,171,861
Messages
5,877,943
Members
433,301
Latest member
MJ_Mac

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