Change all to proper case using VBA - Office 365

Ichokuchi

New Member
Joined
Jul 6, 2018
Messages
37
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!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,057
Office Version
365
Platform
Windows
Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,884
Office Version
2013
Platform
Windows
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

New Member
Joined
Jul 6, 2018
Messages
37
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,057
Office Version
365
Platform
Windows
If you still get that problem... try this ... but should not be necessary

Code:
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
 
Last edited:

Ichokuchi

New Member
Joined
Jul 6, 2018
Messages
37
Code:
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

New Member
Joined
Jul 6, 2018
Messages
37
If you still get that problem... try this ... but should not be necessary

Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,057
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 6, 2018
Messages
37
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,684
Messages
5,470,114
Members
406,681
Latest member
sachinmasurkar

This Week's Hot Topics

Top