Changing Text Case

[Paul Cooke]

Hi Guys,

Hopefully someone can help me out with this one please, I have looked through the forum but can't find a relative thread to my question


I have a sheet covering various ranges A3 down to A37 through to E3 - E37

All these cells contain text

What I would like to do is automaically change any entries made in the cells to Proper case.

Is there anyway of doing this? I have tried the PROPER case function but this does not work for what I need.

Many thanks

Paul

[StephenR]

Like so perhaps?

Please Login or Register  to view this content.

[Paul Cooke]

Hi Stephen, Thanks for the reply - I should of said my knowledge of is basic !!

what do I do with the code ?

Many thanks

Paul

[StephenR]

From your worksheet, press Alt+F11 which opens the VB editor window. Insert menu > Module and paste the code into the white space which appears. To run the macro, from the worksheet Tools > Macro > Macros and select x (change to a more meaningful name).

[sweep]

Hi,

Hold down ALT and tap F11

Do Insert > Module

paste the code

use the "X" in the top right to close the VBA editor

From your workbook go to Tools > Macro > Run Macro

to activate the code



If you want the range to update automatically,

Hold down ALT and tap F11

Select "ThisWorkbook"

View > Code

Please Login or Register  to view this content.

use the "X" in the top right to close the VBA editor

[Paul Cooke]

Many thanks guys - those options both work a treat and I have learnt something new !!

I appriciate your time in replying

Best wishes

Paul

[Paul Cooke]

Me again - sorry guys just a quick follow on ...

is there an issue with security settings that stops this code working if I close theworkbook and then re-open it later, as I have tried this and the code stopes working.

I am using Excel 2007 to design the workbook and saving it as a "Excel 97-2003" so that others can use it.

Each time I save i then reopen it the code seems to stop even though after going into the Alt- F11 bit the code is still there?

sorry to be a pain !

Thanks

[StephenR]

Which code are you using? Does it do anything at all? From my limited use of 2007 it does sometimes appear to disable macros by default, but it should tell you if it does.

[Paul Cooke]

Hi Stephen, thanks for replying again

I'm using the code Sweep posted ..

Please Login or Register  to view this content.

When I open the workbook is does state macros are disabled but I choose to enable them. I enter something in a cell say A3 as help and it becomes Help which is what I wanted but if I close the workbook and reopen it this is when the code stops working - sorry if I'm not making this very clear !

The end result of this workbook is that it will be shared between 3 users 2 using office 2007 and one on 2003 hence theneed to save it as an Excel 97-2003 file format.

Thanks again

Paul