Change all to proper case using VBA - Office 365

[Ichokuchi]

Ok so I am trying to automatically have my form in excel change things to proper case when entered. I found a code that works but then seems to crash excel or kick me to debug here is what I have please help in fixing it so it works.

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140603
Target.Value = Application.WorksheetFunction.Proper(Target.Value)
End Sub


- The line that when i go to debug that seems to be the issue is this one: Target.Value = Application.WorksheetFunction.Proper(Target.Value)


Please help! Thanks!

 

[Yongle]

Private Sub Worksheet_Change(ByVal Target As Range)
    With Application
        .EnableEvents = False
        Target.Value = .WorksheetFunction.Proper(Target.Value)
        .EnableEvents = True
    End With
End Sub

Explanation
Disabling "Events" prevent a change made by the code from triggering a fresh "Event"

 

[Michael M]

Is it an actual UserForm or a cell on the worksheet ??
If the code is in the sheet module and it is a cell on the worksheet it works fine for me !!

Also, this code won't work if you try to apply it to multiple cells at a time !!....use one cell at a time

 

[Ichokuchi]

Thank you, I will try that when I get back to work tomorrow. Hopefully it works

 

[Ichokuchi]

It's a form with several cells and it works on all of them until I tried to erase anything then it errors up. But maybe with code from above I will not get anymore errors

 

[Yongle]

If you still get that problem... try this ... but should not be necessary

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#ff0000]If Target.Value = vbNullString Then Exit Sub[/COLOR]
    With Application
        .EnableEvents = False
        Target.Value = .WorksheetFunction.Proper(Target.Value)
        .EnableEvents = True
    End With
End Sub

 

[Ichokuchi]

Private Sub Worksheet_Change(ByVal Target As Range)
    With Application
        .EnableEvents = False
        Target.Value = .WorksheetFunction.Proper(Target.Value)
        .EnableEvents = True
    End With
End Sub

Explanation
Disabling "Events" prevent a change made by the code from triggering a fresh "Event"

Ok I tried that and I still get the debug/end window popping up.

 

[Ichokuchi]

If you still get that problem... try this ... but should not be necessary

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#ff0000]If Target.Value = vbNullString Then Exit Sub[/COLOR]
    With Application
        .EnableEvents = False
        Target.Value = .WorksheetFunction.Proper(Target.Value)
        .EnableEvents = True
    End With
End Sub

So I tried this, and I don't get debug window, it also doesnt change my things to Proper so seems a step back lol.

 

[Yongle]

I need to understand EXACTLY how cells are being updated

1. Which version of Excel are you using?
2. Please post your userform code using code tags
- to post code click on # icon above post window and paste your code BETWEEN the code tags

thanks

 

[Ichokuchi]

I need to understand EXACTLY how cells are being updated

1. Which version of Excel are you using?
2. Please post your userform code using code tags
- to post code click on # icon above post window and paste your code BETWEEN the code tags

thanks

I am using office 365, I'm confused what is a userform code? cells are being updated by clicking in the part of the form needs updated, and typing or pasting into. the original code worked changing things to proper, but if I clear contents or anything it brings up the end/debug window. I'm trying to eliminate that from happening if I were the only one using this form it wouldnt matter.