Solved automatically capitalizing 1st letter

June 3, 2014 at 10:14:28
Specs: Windows 7
With the use of the macro:
Private Sub Worksheet_Change(ByVal Target As Range)
Target = Application.WorksheetFunction.Proper(Target)
End Sub

How can you modify it to not force lower case when you want it to uppercase. For example:

McAfee instead of Mcafee.
The macro above forces it to be Mcafee even when you manually select an uppercase A.
Other examples: La'Niya or La'niya


See More: automatically capitalizing 1st letter

Report •


#1
June 3, 2014 at 12:48:06
The only way I can think of, while using the Proper function,
is to separate the two parts with a space.

IE: Mcafee to Mc afee

With a space between the sections, you get Mc Afee

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
June 3, 2014 at 13:14:21
✔ Best Answer
First, your code should have be written as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
'Disable Events
  Application.EnableEvents = False
'Apply PROPER function to String
     Target = Application.WorksheetFunction.Proper(Target)
'Enable Events
  Application.EnableEvents = True
End Sub

Without using Application.EnableEvents = False at the start, the change made by the PROPER function is going to considered a “change” and the code is going to run again and again and again on the same cell. In many cases, you may not notice it, but in some cases (like mine) it could crash Excel or cause other problems. I run Excel 2010 on a virtual machine (i.e. in the cloud) and if I don’t disable Events at the start and then turn them back on at the end, Excel crashes rather violently. It's a good programming practice to disable events in cases like this to prevent problems. You just have to make sure that you enable Events before the Sub ends to ensure that the code will run the next time you want it to.

As for your question, if you want to force the first character to be uppercase and the rest of the string to be left alone, this should do it for you:

Private Sub Worksheet_Change(ByVal Target As Range)
'Disable Events
  Application.EnableEvents = False
'Set first character to Uppercase, leave the rest alone
   Target = UCase(Left(Target, 1)) & Right(Target, Len(Target) - 1)
'Enable Events
 Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
June 3, 2014 at 15:42:29
Thank you. I will check it out tomorrow. I can't say it was my code, but I can understand your point. I got the code from one of the postings on this web site. I haven't done any actual programming in years.

Report •

Related Solutions

#4
June 4, 2014 at 09:52:07
Actually, I looked back at where the original code came from and it was something that I posted a while back.

In my defense, I did note that the code was very basic and needed to be fleshed out based on what the original requester was trying to do.

I guess the next I do something like that I should follow my own advice and at least post code that follows good programming practices, even if I know that the code will be "enhanced" later on.

In any case, let me know if you need anything else.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •


Ask Question