Sentence case

Hi all,

How do I enforce for ranges A1:A10 and C1:C10 that whatever is entered in these cells is changed to sentence case, i.e. "today it is Raining." will change to "Today it is raining.".

I thought of having helper columns with the following formula that would then paste over the ranges on a Workbook.close event but it seems long-winded and not the right way of doing it.

Formula for helper columns:

Please Login or Register  to view this content.

Any VBA ideas?

Use

Please Login or Register  to view this content.

Hi Mick/Skt,

I'm placing your respective codes in Sheet1 but nothing is occurring? Am I missing something. I added it to ThisWorkbook but the same thing.

See attached file:-
The code is set in the Sheet module for your Data.

Hi Mick,
in
This is quite odd. I have no doubt that it's working however when I type (without quotes) 'hello This is Here' in A6 it remains exactly the same?

It may be I stepped through the code and did not get to the end to "Enable Events" if you copy that last line

Please Login or Register  to view this content.

and just run it in a button on the sheet. i think it will fix it.

Hi Mick,

Yep - working! How is this so? I am using this instead of the button. Would you mind explaining?

Please Login or Register  to view this content.

That basically is the same as my first code , except you are calling a sub instead of directly running that bit of code.

You could still get the same problem as before, if you where to step through only half the code, because by not completing the code and setting the "EnableEvents" to true, all subsequent Change events would not be able to run.

The purpose of the "Application.EnableEvents" code is to stop the code from running an endless loop. When you alter the contents of the cell you change its value, this triggers the code to run again, this can then repeats itself endlessly.

You really need to also change the similar line as below, because if you select a "Range" of cells you will get an error.

Please Login or Register  to view this content.

Thank you. The only thing I don't understand is how someone (other users) could only step through half the code? They would just be entering/amending cell values so the macro would fire and that would be it?

Would I change it to:

Please Login or Register  to view this content.

No, it only if the developer was playing with the code by stepping through it "F8" that he might leave the "EnableEvent" at False, other than that it would/should not happen.

That line of code need to be "= 1" to stop the code if the Range (number) of cells Selected was greater than 1.

Thank you Mick and thank you for explaining it to me. Very kind.

You're welcome
Regrds Mick