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]

Are you simply entering values in cells?

 

[Ichokuchi]

Are you simply entering values in cells?

in cells from a user created form.

 

[Yongle]

what is a user created form?

 

[Ichokuchi]

what is a user created form?

A form someone created using excel, that is attached to another form to gather data from that form. cells are merged together for this or that. all this is besides the point though, as I stated the original code works with the exception of that box popping up to debug or end if contents are cleared to put something different in.

 

[Yongle]

If I an trying to help you my questions are not besides the point - I do not ask them for fun

Next test:
add a new sheet and put the VBA behind that sheet - use VBA from post#6
see what happens when you add or amend text etc
Does the bug still occur?

 

[Ichokuchi]

If I an trying to help you my questions are not besides the point - I do not ask them for fun

Next test:
add a new sheet and put the VBA behind that sheet - use VBA from post#6
see what happens when you add or amend text etc
Does the bug still occur?

Sorry wasnt trying to discredit or upset or anything.
So when I do it to a new sheet, it works 100% I can clear things add things, but I need it to work with the old one as there is a lot going on in that which would take way to much time to recreate lol.
Its amazing to see it work though on that new sheet

 

[Yongle]

Ok - so something on your original sheet is doing something tha affect the way the macro is working
As you can now see - my question was not besides the point

If you work out what is different between the cell in the original sheet and the cell in the new sheet and tell me, then we can solve the problem

 

[Ichokuchi]

Sorry wasnt trying to discredit or upset or anything.
So when I do it to a new sheet, it works 100% I can clear things add things, but I need it to work with the old one as there is a lot going on in that which would take way to much time to recreate lol.
Its amazing to see it work though on that new sheet

So I took a screenshot of what happens on original form with the code from post #6 when trying to clear contents of something

[/URL][/IMG]

 

[Ichokuchi]

Maybe its because the cells in the original as you can see in image above are merged?

 

[Ichokuchi]

Maybe its because the cells in the original as you can see in image above are merged?

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