modify code to make all words Proper case , not just all Upper case

steve400243

Active Member
Joined
Sep 15, 2016
Messages
429
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello, I use this code to make all letters capitol letters, Need to modify it so that it changes all words to proper case. I cannot figure it out with multiple google searches.

For instance HAPPY DAYS to Happy Days. Thanks for your help forum Experts.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell   As Range
    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
        cell = vbProperCase(cell)
    Next
    Application.EnableEvents = True
End Sub


Sub RunOnce()
Dim c As Range
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
    c = vbProperCase(c)
Next
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Try this.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
        cell.Value = StrConv(cell.Value, vbProperCase)
    Next
    Application.EnableEvents = True

End Sub
 
Upvote 0
That did it, thank you for looking at it Norie!

For Each cell In Target
cell.Value = StrConv(cell.Value, vbProperCase)
 
Upvote 0
Try this.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
        cell.Value = StrConv(cell.Value, vbProperCase)
    Next
    Application.EnableEvents = True

End Sub

would there be a way to exclude column B?
 
Upvote 0
would there be a way to exclude column B?

One way:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
         If cell.Column <> 2 Then
             cell.Value = StrConv(cell.Value, vbProperCase)
         End If
    Next
    Application.EnableEvents = True

End Sub
 
Last edited:
Upvote 0
Try this.
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
        cell.Value = StrConv(cell.Value, vbProperCase)
    Next
    Application.EnableEvents = True

End Sub
I would recommend using Excel's PROPER function instead...

cell.Value = Application.Proper(cell.Value)

as it handles the proper casing of text better. One example (there are a couple of others which I don't remember offhand) would be (note the first letter after the left parenthesis)...

one two (three) four

StrConv will return...

One Two (three) Four

whereas Excel's PROPER function will return...

One Two (Three) Four
 
Upvote 0
Hi Rick, This is not working for the additional code to exclude columnB?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell   As Range
    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
    If cell.Column <> 2 Then
    cell.Value = StrConv(cell.Value, vbProperCase)
    Next
    Application.EnableEvents = True
End Sub
 
Upvote 0
Hi Rick, This is not working for the additional code to exclude columnB?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell   As Range
    On Error Resume Next
    Application.EnableEvents = False
    For Each cell In Target
      If cell.Column <> 2 Then
        cell.Value = StrConv(cell.Value, vbProperCase)
      [B][COLOR="#FF0000"]End If[/COLOR][/B]
    Next
    Application.EnableEvents = True
End Sub
It is always a good idea to include the error number and error description when your code is generating an error. I also find full indenting of code makes it easier to spot the problem. In your case, you were missing the End If statement that I show in red above. I notice you did not take my suggestion and replace the StrConv function with Excel's PROPER function. While it is your choice whether to do so or not, of course, but I really would recommend making the change.
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

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