Changing Text Case

**Paul Cooke** · 11-05-2008, 08:22 AM

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** · 11-05-2008, 08:37 AM

Like so perhaps?

Sub x()
Dim rng As Range
For Each rng In ActiveSheet.UsedRange
    If VarType(rng) = vbString Then rng = WorksheetFunction.Proper(rng)
Next rng
End Sub

**Paul Cooke** · 11-05-2008, 08:42 AM

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** · 11-05-2008, 08:53 AM

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** · 11-05-2008, 08:56 AM

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

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("A3:E37")) Is Nothing Then Target.Value = Application.WorksheetFunction.Proper(Target.Value)
End Sub

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

**Paul Cooke** · 11-05-2008, 09:37 AM

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** · 11-05-2008, 10:08 AM

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

Changing Text Case

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Changing Text Case

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions