Auto Capitalization of movie names.

[DaveBen]

Hi, I am wondering how I can get excel to automatically capitalize words in a cell that I input. I would like to enter the title of DVD/Blu-rays that I have and get it to be proper after I go to the next cell. For instance I would like to enter: it's a wonderful life and get It's a Wonderful Life. The way I have it now it will go "It'S A Wonderful Life". Also I would another colum of cells to also auto capitalize words I type in like: "sci-fi to Sci-Fi, comedy to Comedy etc". Any help would be greatly appreciated. Thanks in advance.

Dave

 

[DaveBen]

Thanks. How would I get it to do that for another column that is two columns removed from the first? Also how do I get it not to capitalize "'S"?

Dave

 

[pleeseemailme]

Jeffreybrown,
I'm note sure if this is against forum rules, but I'm learning VBA and had a question about your code. Let me know if I need to post in another thread. Anyway, I understand everything about your VBA code except the below line. When would the count of the target exceed one? When more than one cell is changed in the target range?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Set Rng = Target.Parent.Range("A1:A11")
[COLOR=#ff0000]    If Target.Count > 1 Then Exit Sub[/COLOR]
    If Intersect(Target, Rng) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target = Application.WorksheetFunction.Proper(Target)
    Application.EnableEvents = False
End Sub

 

[jeffreybrown]

Jeffreybrown,
I'm note sure if this is against forum rules, but I'm learning VBA and had a question about your code. Let me know if I need to post in another thread. Anyway, I understand everything about your VBA code except the below line. When would the count of the target exceed one? When more than one cell is changed in the target range?

Yes, that is correct, when more than one cell is selected.

 

[DaveBen]

I do believe that it exceeds 1 when you enter anything in the field. As with binary 0 = no data and 1 = data.

 

[jeffreybrown]

Hi Dave Ben,

To a certain point you have exceed my VBA capablities.

For multiple ranges, this seem to do the trick...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim RngA As Range, RngC As Range, r As Range
    Set RngA = Range("A1:A11")
    Set RngC = Range("C1:C11")
    Set r = Application.Union(RngA, RngC)
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, r) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target = Application.WorksheetFunction.Proper(Target)
    Application.EnableEvents = True
End Sub

For the non capitilization of an "S", this seems to go above my knowledge with VBA. Also, I would say, is this the only exception or could there be more rules to apply?

A UDF might help, but not sure how to do that.

 

[jeffreybrown]

In post #10, that should be...

Application.EnableEvents = False
Target = Application.WorksheetFunction.Proper(Target)
Application.EnableEvents = False

Application.EnableEvents = False
Target = Application.WorksheetFunction.Proper(Target)
[COLOR="#FF0000"]Application.EnableEvents = True[/COLOR]

 

[DaveBen]

Jeff:

I might be doing something wrong. After I entered your updated VBA into the sheet, the columns I want to use are now just leaving everything the way I typed them. The columns I want are C2 down to C63 and F2 down to F63. Hopefully you can help on this.

Dave

 

[jeffreybrown]

Could be because you need to enableevents again if you used my first code. Look at post #16.

To enableevents, open up the VBE, select Ctrl + G which will open up the Immediate window and enter...

Application.EnableEvents = True

at the end of the "True" select enter and now try again.

 

[RoryA]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim RngA As Range
    Dim r As Range
    Set RngA = Range("C2:C63,F2:F63")
    If Not Intersect(Target, RngA) Is Nothing Then
        Application.EnableEvents = False
        For Each r In Intersect(Target, RngA).Cells
            r.Value = StrConv(r.Value, vbProperCase)
        Next r
        Application.EnableEvents = True
    End If
End Sub

 

[DaveBen]

I don't know what I am doing wrong. I copied and pasted both codes into the sheet and they don't work. What am I not doing????

Dave