Change case on Entry

[AMK4]

How can I change the case of text typed into a cell as soon as the cell loses its focus? Basically I have a sheet that requires one to enter names in a column, first and last. Upon exiting that cell (or it losing its focus), that data then gets used in the very next cell to generate a sring consisting of the first 2 letters of the first name and first 3 letters of the last name. What I need to do is make sure the case is proper before I use the data in the next cell.

Correct input in A1: Jack Doppler
Resulting text in A2: JaDop

Wrong input in A1: jACk dOPplEr
Resulting test in A2: JaDop

Either I figure out a way to fix the text that they just typed in as soon as the cell loses its focus (preferred), or I fix the resulting string when it gets created, AND fix the text in A1 at the same time.

For reference, cell A2 contains the following formula:

Please Login or Register  to view this content.

...which checks whether A1 is empty before doing anything. Then it creates the string.

[Arvi Laanemets]

Hi

=IF(A1="","",LEFT(PROPER(A1),MIN(2,FIND(" ",A1)-1))&MID(PROPER(A1),FIND("
",A1)+1,3))

The formula above works for cases the first name is 1 or 2 characters too,
but does fail when a single name is in cell A1


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"AMK4" <[email protected]> wrote in message
news:[email protected]...
>
> How can I change the case of text typed into a cell as soon as the cell
> loses its focus? Basically I have a sheet that requires one to enter
> names in a column, first and last. Upon exiting that cell (or it
> losing its focus), that data then gets used in the very next cell to
> generate a sring consisting of the first 2 letters of the first name
> and first 3 letters of the last name. What I need to do is make sure
> the case is proper before I use the data in the next cell.
>
> Correct input in A1: Jack Doppler
> Resulting text in A2: JaDop
>
> Wrong input in A1: jACk dOPplEr
> Resulting test in A2: JaDop
>
> Either I figure out a way to fix the text that they just typed in as
> soon as the cell loses its focus (preferred), or I fix the resulting
> string when it gets created, AND fix the text in A1 at the same time.
>
> For reference, cell A2 contains the following formula:
>
> Code:
> --------------------
> =IF(ISBLANK(A1),0,CONCATENATE(MID(TRIM(A1), 1, 2), MID(TRIM(A1), FIND("
> ", TRIM(A1)) + 1, 3)))
> --------------------
> ..which checks whether A1 is empty before doing anything. Then it
> creates the string.
>
>
> --
> AMK4
> ------------------------------------------------------------------------
> AMK4's Profile:
> http://www.excelforum.com/member.php...o&userid=19143
> View this thread: http://www.excelforum.com/showthread...hreadid=501933
>

[AMK4]

All good and well, but this doesn't fix the data entered in cell A1. It only fixes the string created in A2. Is there a way I can fix what's in A1 as well?

Hi

=IF(A1="","",LEFT(PROPER(A1),MIN(2,FIND(" ",A1)-1))&MID(PROPER(A1),FIND("
",A1)+1,3))

The formula above works for cases the first name is 1 or 2 characters too,
but does fail when a single name is in cell A1


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

[Gary Keramidas]

i fooled around a little with this, it may or may not do what you want.
right click a sheet name and choose view code
click the dropdown (general) and choose worksheet
paste this on the selection change sheet between private sub and end sub.
then type a name in column A



Dim cell As Range
If Target.Count = 1 Then

For Each cell In Range("A:A")
If cell.Value > "" Then
If Not IsError(Application.Find(" ", cell.Value)) Then
cell.Offset(0, 1).Value = Left(Application.Proper(cell.Value), 2) & _
Mid(Application.Proper(cell.Value), Application.Find(" ", cell.Value) + _
1, 3)
End If
End If
Next
End If

--


Gary


"AMK4" <[email protected]> wrote in message
news:[email protected]...
>
> How can I change the case of text typed into a cell as soon as the cell
> loses its focus? Basically I have a sheet that requires one to enter
> names in a column, first and last. Upon exiting that cell (or it
> losing its focus), that data then gets used in the very next cell to
> generate a sring consisting of the first 2 letters of the first name
> and first 3 letters of the last name. What I need to do is make sure
> the case is proper before I use the data in the next cell.
>
> Correct input in A1: Jack Doppler
> Resulting text in A2: JaDop
>
> Wrong input in A1: jACk dOPplEr
> Resulting test in A2: JaDop
>
> Either I figure out a way to fix the text that they just typed in as
> soon as the cell loses its focus (preferred), or I fix the resulting
> string when it gets created, AND fix the text in A1 at the same time.
>
> For reference, cell A2 contains the following formula:
>
> Code:
> --------------------
> =IF(ISBLANK(A1),0,CONCATENATE(MID(TRIM(A1), 1, 2), MID(TRIM(A1), FIND("
> ", TRIM(A1)) + 1, 3)))
> --------------------
> ..which checks whether A1 is empty before doing anything. Then it
> creates the string.
>
>
> --
> AMK4
> ------------------------------------------------------------------------
> AMK4's Profile:
> http://www.excelforum.com/member.php...o&userid=19143
> View this thread: http://www.excelforum.com/showthread...hreadid=501933
>

[AMK4]

Aha! Thanks! It works... Now two things:

1) I noticed it being a bit slow and I can only assume it's because it's running through the entire column each time one of the cells change. Is there a way to have it only affect the changd cell as opposed to scanning the whole column?

2) I will also be entering data in the 3rd column which I'd also like to have .Proper run on. At the moment, the only way to do that is to run yet another For loop, just like yours, to do that. This adds to the time it takes updating the entire column.

If there's a way to update and affect just the (current) changed cell, that'd be great. Suggestions?

i fooled around a little with this, it may or may not do what you want.
right click a sheet name and choose view code
click the dropdown (general) and choose worksheet
paste this on the selection change sheet between private sub and end sub.
then type a name in column A

[Arvi Laanemets]

Hi

Into free column (row1) enter the formula like
=PROPER(A1)
and copy down.
Copy the range with formulas, right-click on A1, and select
PasteSpecial>Values - OK.
Delete the column with formulas.

Modify the formula in column B, like
=IF(A1="","",LEFT(A1,MIN(2,FIND(" ",A1)-1))&MID(A1,FIND(" ",A1)+1,3))
(because you don't to use PROPER there anymore)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"AMK4" <[email protected]> wrote in message
news:[email protected]...
>
> All good and well, but this doesn't fix the data entered in cell A1. It
> only fixes the string created in A2. Is there a way I can fix what's in
> A1 as well?
>
>
> Arvi Laanemets Wrote:
>> Hi
>>
>> =IF(A1="","",LEFT(PROPER(A1),MIN(2,FIND("
>> ",A1)-1))&MID(PROPER(A1),FIND("
>> ",A1)+1,3))
>>
>> The formula above works for cases the first name is 1 or 2 characters
>> too,
>> but does fail when a single name is in cell A1
>>
>>
>> --
>> Arvi Laanemets
>> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>
>
> --
> AMK4
> ------------------------------------------------------------------------
> AMK4's Profile:
> http://www.excelforum.com/member.php...o&userid=19143
> View this thread: http://www.excelforum.com/showthread...hreadid=501933
>

[Gary Keramidas]

see if this is any better. delete the other code or paste this over the top,
don't keep both

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Count = 1 Then

If Not Intersect(Target, Range("A:A")) Is Nothing Then

If Not IsError(Application.Find(" ", Target.Value)) Then
Target.Offset(0, 1).Value = Left(Application.Proper(Target.Value), 2) & _
Mid(Application.Proper(Target.Value), Application.Find(" ", Target.Value) +
_
1, 3)
Target.Offset(0, 2).Value = Application.Proper(Target.Offset(0, 2).Value)
End If
End If
End If
End Sub

--


Gary


"AMK4" <[email protected]> wrote in message
news:[email protected]...
>
> Aha! Thanks! It works... Now two things:
>
> 1) I noticed it being a bit slow and I can only assume it's because
> it's running through the entire column each time one of the cells
> change. Is there a way to have it only affect the changd cell as
> opposed to scanning the whole column?
>
> 2) I will also be entering data in the 3rd column which I'd also like
> to have .Proper run on. At the moment, the only way to do that is to
> run yet another For loop, just like yours, to do that. This adds to
> the time it takes updating the entire column.
>
> If there's a way to update and affect just the (current) changed cell,
> that'd be great. Suggestions?
>
>
> Gary Keramidas Wrote:
>> i fooled around a little with this, it may or may not do what you want.
>> right click a sheet name and choose view code
>> click the dropdown (general) and choose worksheet
>> paste this on the selection change sheet between private sub and end
>> sub.
>> then type a name in column A
>
>
> --
> AMK4
> ------------------------------------------------------------------------
> AMK4's Profile:
> http://www.excelforum.com/member.php...o&userid=19143
> View this thread: http://www.excelforum.com/showthread...hreadid=501933
>

[AMK4]

Thanks Gary. If I'm reading this correctly, then the C-column will only get .Proper run on it when the A-column changes. While I'd like to be able to do this, I can't enter data in both columns A and C at the same time. Data may be entered in different ways, either everything in A-column first, then stuff in C-column, or row by row. I just don't know how people are going to work with this.

Would it matter if I duplicate the If Not Intersect loop a second time, use "C:C" as the range and change the .Proper line to read Target.Value = Application.Proper(Target.Value) or will that conflict with the Target in the previous loop?

see if this is any better. delete the other code or paste this over the top,
don't keep both

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Count = 1 Then

If Not Intersect(Target, Range("A:A")) Is Nothing Then

If Not IsError(Application.Find(" ", Target.Value)) Then
Target.Offset(0, 1).Value = Left(Application.Proper(Target.Value), 2) & _
Mid(Application.Proper(Target.Value), Application.Find(" ", Target.Value) +
_
1, 3)
Target.Offset(0, 2).Value = Application.Proper(Target.Offset(0, 2).Value)
End If
End If
End If
End Sub

--


Gary

[AMK4]

Ugh. This is driving me up the wall. I simply can't get it to work the way I'd like it to. All of the suggestions given here are all good, however they all work for the first step of the process. I need it to be expanded and optimized if you will. Right now it runs the entire course of a column (I think) and thus causes a delay when you type a name in and hit return.

Picture 3 columns, D, E, and F, each one 30 rows (4:34). Column D will be used to enter a person's name. Column E will be used to programmatically generate a string consisting of 2 and 3 letters of their first and last name respectively. And Column F will be used to enter the name of that person's parents.

When I enter a name in Column D, I want to run:

Please Login or Register  to view this content.

That's it. Now, when I enter a name in Column F, I want to run:

Please Login or Register  to view this content.

And that's it. One more step to finish it all. If I delete a name from Column D, I want to run:

Please Login or Register  to view this content.

Basically, clear out the other two cells as well. All of this on a cell change I guess, which at the moment I've been trying to make work by having a Worksheet_Change() sub. But I can't for the life of me figure out how to do this at the same time. Any help would be greatly appreciated.

Edited to add: I think the fact that I'm trying to run Application.Proper on the cell causes it to consider it another change, and thus the whole thing repeats again .. and again .. and again ..

[Gary Keramidas]

see if this works like you want, then i'll work on blanking out the entres



Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

If Target.Count = 1 Then

If Not Intersect(Target, Range("D4:D34")) Is Nothing Then

If Not IsError(Application.Find(" ", Target.Value)) Then
Target.Offset(0, 1).Value = Left(Application.Proper(Target.Value), 2) & _
Mid(Application.Proper(Target.Value), Application.Find(" ", Target.Value) +
_
1, 3)
End If
End If
If Not Intersect(Target, Range("f4:f34")) Is Nothing Then
Target.Value = Application.Proper(Target.Value)

End If
End If
End Sub


--


Gary


"AMK4" <[email protected]> wrote in message
news:[email protected]...
>
> Ugh. This is driving me up the wall. I simply can't get it to work the
> way I'd like it to. All of the suggestions given here are all good,
> however they all work for the first step of the process. I need it to
> be expanded and optimized if you will. Right now it runs the entire
> course of a column (I think) and thus causes a delay when you type a
> name in and hit return.
>
> Picture 3 columns, D, E, and F, each one 30 rows (4:34). Column D will
> be used to enter a person's name. Column E will be used to
> programmatically generate a string consisting of 2 and 3 letters of
> their first and last name respectively. And Column F will be used to
> enter the name of that person's parents.
>
> When I enter a name in Column D, I want to run:
> Code:
> --------------------
> ::
> cell.Value = Application.Proper(cell.Value)
> ' Fill E column with 2+3 string
> cell.Offset(0, 1).Value = Left(call.Value, 2) & _
> mid(cell.Value), Application.Find(\" \", cell.Value) + 1, 3)::
> --------------------
>
>
> That's it. Now, when I enter a name in Column F, I want to run:
> Code:
> --------------------
> ::
> cell.Value = Application.Proper(cell.Value)::
> --------------------
>
>
> And that's it. One more step to finish it all. If I delete a name
> from Column D, I want to run:
> Code:
> --------------------
> ::
> cell.Offset(0, 1).Value = \"\"
> cell.Offset(0, 2).Value = \"\"::
> --------------------
>
>
> Basically, clear out the other two cells as well. All of this on a
> cell change I guess, which at the moment I've been trying to make work
> by having a Worksheet_Change() sub. But I can't for the life of me
> figure out how to do this at the same time. Any help would be greatly
> appreciated.
>
>
> --
> AMK4
> ------------------------------------------------------------------------
> AMK4's Profile:
> http://www.excelforum.com/member.php...o&userid=19143
> View this thread: http://www.excelforum.com/showthread...hreadid=501933
>

[Gary Keramidas]

try this and see how it works for you:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

If Target.Count = 1 Then

If Not Intersect(Target, Range("D4:D34")) Is Nothing Then

If Not IsError(Application.Find(" ", Target.Value)) Then
Target.Offset(0, 1).Value = Left(Application.Proper(Target.Value), 2) & _
Mid(Application.Proper(Target.Value), Application.Find(" ", Target.Value) +
_
1, 3)
ElseIf Target.Value = "" Then
Range(Cells(Target.Row, "d"), Cells(Target.Row, "f")) = ""
End If
End If
If Not Intersect(Target, Range("f4:f34")) Is Nothing Then
Target.Value = Application.Proper(Target.Value)

End If
End If
End Sub


--


Gary


"AMK4" <[email protected]> wrote in message
news:[email protected]...
>
> Ugh. This is driving me up the wall. I simply can't get it to work the
> way I'd like it to. All of the suggestions given here are all good,
> however they all work for the first step of the process. I need it to
> be expanded and optimized if you will. Right now it runs the entire
> course of a column (I think) and thus causes a delay when you type a
> name in and hit return.
>
> Picture 3 columns, D, E, and F, each one 30 rows (4:34). Column D will
> be used to enter a person's name. Column E will be used to
> programmatically generate a string consisting of 2 and 3 letters of
> their first and last name respectively. And Column F will be used to
> enter the name of that person's parents.
>
> When I enter a name in Column D, I want to run:
> Code:
> --------------------
> ::
> cell.Value = Application.Proper(cell.Value)
> ' Fill E column with 2+3 string
> cell.Offset(0, 1).Value = Left(call.Value, 2) & _
> mid(cell.Value), Application.Find(\" \", cell.Value) + 1, 3)::
> --------------------
>
>
> That's it. Now, when I enter a name in Column F, I want to run:
> Code:
> --------------------
> ::
> cell.Value = Application.Proper(cell.Value)::
> --------------------
>
>
> And that's it. One more step to finish it all. If I delete a name
> from Column D, I want to run:
> Code:
> --------------------
> ::
> cell.Offset(0, 1).Value = \"\"
> cell.Offset(0, 2).Value = \"\"::
> --------------------
>
>
> Basically, clear out the other two cells as well. All of this on a
> cell change I guess, which at the moment I've been trying to make work
> by having a Worksheet_Change() sub. But I can't for the life of me
> figure out how to do this at the same time. Any help would be greatly
> appreciated.
>
>
> --
> AMK4
> ------------------------------------------------------------------------
> AMK4's Profile:
> http://www.excelforum.com/member.php...o&userid=19143
> View this thread: http://www.excelforum.com/showthread...hreadid=501933
>

[AMK4]

try this and see how it works for you:

Garry,

It works, except it doesn't run the Target cell through Application.Proper as well. And I think that's where the delay problem comes into play. Since a) I'm making a change by typing something in, and b) the macro itself makes a second change when it runs Proper on it. I don't know how to avoid that delay (or recursive loop if that's what it is.) But when I stick a MsgBox line in the If Then statement and run the macro, that MsgBox comes up a a whole lot more than those 30 rows before it finally quits (I stopped counting at 100 times)...

[Gary Keramidas]

don't know why it does that for data in column F. i don't see any slowdown,
though, probably because i have an athlon64 x2. it seems to iterate 199
times.
tom or bob, or norman or one of those others may be able to shed some light
on it.
--


Gary


"AMK4" <[email protected]> wrote in message
news:[email protected]...
>
> Gary Keramidas Wrote:
>> try this and see how it works for you:
>
> Garry,
>
> It works, except it doesn't run the Target cell through
> Application.Proper as well. And I think that's where the delay problem
> comes into play. Since a) I'm making a change by typing something in,
> and b) the macro itself makes a second change when it runs Proper on
> it. I don't know how to avoid that delay (or recursive loop if that's
> what it is.) But when I stick a MsgBox line in the If Then statement
> and run the macro, that MsgBox comes up a a whole lot more than those
> 30 rows before it finally quits (I stopped counting at 100 times)...
>
>
> --
> AMK4
> ------------------------------------------------------------------------
> AMK4's Profile:
> http://www.excelforum.com/member.php...o&userid=19143
> View this thread: http://www.excelforum.com/showthread...hreadid=501933
>

[AMK4]

don't know why it does that for data in column F. i don't see any slowdown,
though, probably because i have an athlon64 x2. it seems to iterate 199
times.
tom or bob, or norman or one of those others may be able to shed some light
on it.

Yes, entering data in column F will cause that iteration...for whatever reason. But that's not what I was talking about. I want to have the first Target cell (column D) to also have Application.Proper run on. And as soon as I add that extra bit in the If ... End If bit, I start getting iterations. And as you noticed, the same thing happens with column F.

I hope someone has some way around this.

[Norman Jones]

Hi AMK4,

Try:

On Error GoTo XIT
Appplication.EnableEvents = False

'Your code

XIT:
Appplication.EnableEvents = True


---
Regards,
Norman



"AMK4" <[email protected]> wrote in message
news:[email protected]...
>
> Gary Keramidas Wrote:
>> don't know why it does that for data in column F. i don't see any
>> slowdown,
>> though, probably because i have an athlon64 x2. it seems to iterate
>> 199
>> times.
>> tom or bob, or norman or one of those others may be able to shed some
>> light
>> on it.
>
> Yes, entering data in column F will cause that iteration...for whatever
> reason. But that's not what I was talking about. I want to have the
> first Target cell (column D) to also have Application.Proper run on.
> And as soon as I add that extra bit in the If ... End If bit, I start
> getting iterations. And as you noticed, the same thing happens with
> column F.
>
> I hope someone has some way around this.
>
>
> --
> AMK4
> ------------------------------------------------------------------------
> AMK4's Profile:
> http://www.excelforum.com/member.php...o&userid=19143
> View this thread: http://www.excelforum.com/showthread...hreadid=501933
>

[Norman Jones]

Hi AMK4.

Appplication ===> Application


---
Regards,
Norman


"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi AMK4,
>
> Try:
>
> On Error GoTo XIT
> Appplication.EnableEvents = False
>
> 'Your code
>
> XIT:
> Appplication.EnableEvents = True
>
>
> ---
> Regards,
> Norman
>

[Gary Keramidas]

i never got iterations with any other part, only column F. everything worked
here as you asked, but i didn't notice the extra iterations on my pc,
because it happens so fast.


--


Gary


"AMK4" <[email protected]> wrote in message
news:[email protected]...
>
> Gary Keramidas Wrote:
>> don't know why it does that for data in column F. i don't see any
>> slowdown,
>> though, probably because i have an athlon64 x2. it seems to iterate
>> 199
>> times.
>> tom or bob, or norman or one of those others may be able to shed some
>> light
>> on it.
>
> Yes, entering data in column F will cause that iteration...for whatever
> reason. But that's not what I was talking about. I want to have the
> first Target cell (column D) to also have Application.Proper run on.
> And as soon as I add that extra bit in the If ... End If bit, I start
> getting iterations. And as you noticed, the same thing happens with
> column F.
>
> I hope someone has some way around this.
>
>
> --
> AMK4
> ------------------------------------------------------------------------
> AMK4's Profile:
> http://www.excelforum.com/member.php...o&userid=19143
> View this thread: http://www.excelforum.com/showthread...hreadid=501933
>

[Gary Keramidas]

and watch your p's and q's, norman <g>

--


Gary


"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi AMK4.
>
> Appplication ===> Application
>
>
> ---
> Regards,
> Norman
>
>
> "Norman Jones" <[email protected]> wrote in message
> news:[email protected]...
>> Hi AMK4,
>>
>> Try:
>>
>> On Error GoTo XIT
>> Appplication.EnableEvents = False
>>
>> 'Your code
>>
>> XIT:
>> Appplication.EnableEvents = True
>>
>>
>> ---
>> Regards,
>> Norman
>>
>
>

[Gary Keramidas]

norman's tip seems to work here. thanks norman

--


Gary


"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi AMK4.
>
> Appplication ===> Application
>
>
> ---
> Regards,
> Norman
>
>
> "Norman Jones" <[email protected]> wrote in message
> news:[email protected]...
>> Hi AMK4,
>>
>> Try:
>>
>> On Error GoTo XIT
>> Appplication.EnableEvents = False
>>
>> 'Your code
>>
>> XIT:
>> Appplication.EnableEvents = True
>>
>>
>> ---
>> Regards,
>> Norman
>>
>
>

[AMK4]

Norman? You're a genius! Works like a charm! (after the removal of stowaway p's)

Thank you, thank you, thank you!

Hi AMK4,

Try:

On Error GoTo XIT
Appplication.EnableEvents = False

'Your code

XIT:
Appplication.EnableEvents = True


---
Regards,
Norman