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!

 

[Ichokuchi]

That is what is going on, so any ideas? I merged cells on the sample sheet and i get that error.

Yongle you are a rockstar in helping me figure out the problem, to work with merged cells I used this.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target(1).Value = vbNullString Then Exit Sub
With Application
.EnableEvents = False
Target(1).Value = .WorksheetFunction.Proper(Target(1).Value)
.EnableEvents = True
End With
End Sub

You see i added (1) after target before the . and it works!

 

[Yongle]

Well done! Merged cells cause lots of problems with VBA - avoid using them if possible. Thanks for the feedback

One final comment
- please use code tags when posting code - it is easy - click on # icon above post window and paste your code BETWEEN the code tags.

 

[Michael M]

AND...instead of using merged cells, format the multiple cells by....
Highlight the cells
Rclick Format cells
alignment tab
Horizontal dropdown
Center across selection

They will then look like they are merged