Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Event Only Affects One Column
Hello:
One column is dedicated to phone number. When I copy-paste phone number to it, I want all phone numbers in the same format like 999-999-9999. I don't want to see 999.999.9999 or (999) 999-9999 or (999)-999-9999 I want this event gets triggered only after I paste the phone number into a cell in the column and hit RETURN Could you help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Event Only Affects One Column
Select whole column
FormatCellNumber choose Customized Enter: 000-000-0000 Et voilà! HTH -- AP "Who I Am" a écrit dans le message de ups.com... Hello: One column is dedicated to phone number. When I copy-paste phone number to it, I want all phone numbers in the same format like 999-999-9999. I don't want to see 999.999.9999 or (999) 999-9999 or (999)-999-9999 I want this event gets triggered only after I paste the phone number into a cell in the column and hit RETURN Could you help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Event Only Affects One Column
Sorry it does not work.
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Event Only Affects One Column
Did you enter your phone numbers as decimals?
You must not include any - when typing your numbers. You say it does not work.. what is the result? Is there an error msg? HTH -- AP "Who I Am" a écrit dans le message de oups.com... Sorry it does not work. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Event Only Affects One Column
The issue here is, I guess, that I do not type the phone number into a
cell in the column (if I type it, of course I will follow my own format). I copy-paste them from different sources. They may look like 999.999.9999 or (999)-999-9999 I want a macro to change the format after I hit ENTER. Per your suggestion, I format the column as 000-000-0000. But it does not work. Thank you for your prompt reply. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Event Only Affects One Column
Try this Worksheet event: assumes data in column A
Private Sub Worksheet_Change(ByVal Target As Range) dim strphone as string On Error GoTo wsexit: Application.EnableEvents = False Set isect = Application.Intersect(Range("A:A"), Target) '<=== change A as required If isect Is Nothing Then ' MsgBox "Ranges do not intersect" Else strphone = Target.Value strphone = Replace(strphone, ".", "") strphone = Replace(strphone, "-", "") strphone = Replace(strphone, "(", "") strphone = Replace(strphone, ")", "") Target.Value = Format(strphone, "000-000-0000") End If wsexit: Application.EnableEvents = True End Sub "Who I Am" wrote: Hello: One column is dedicated to phone number. When I copy-paste phone number to it, I want all phone numbers in the same format like 999-999-9999. I don't want to see 999.999.9999 or (999) 999-9999 or (999)-999-9999 I want this event gets triggered only after I paste the phone number into a cell in the column and hit RETURN Could you help? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Event Only Affects One Column
Better ...
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo wsexit: Application.EnableEvents = False Set myRange = Intersect(Target, Range("A:A")) If Not myRange Is Nothing Then For Each mycell In myRange With mycell strphone = .Value strphone = Replace(strphone, ".", "") strphone = Replace(strphone, "-", "") strphone = Replace(strphone, "(", "") strphone = Replace(strphone, ")", "") .Value = Format(strphone, "000-000-0000") End With Next mycell End If wsexit: Application.EnableEvents = True End Sub "Toppers" wrote: Try this Worksheet event: assumes data in column A Private Sub Worksheet_Change(ByVal Target As Range) dim strphone as string On Error GoTo wsexit: Application.EnableEvents = False Set isect = Application.Intersect(Range("A:A"), Target) '<=== change A as required If isect Is Nothing Then ' MsgBox "Ranges do not intersect" Else strphone = Target.Value strphone = Replace(strphone, ".", "") strphone = Replace(strphone, "-", "") strphone = Replace(strphone, "(", "") strphone = Replace(strphone, ")", "") Target.Value = Format(strphone, "000-000-0000") End If wsexit: Application.EnableEvents = True End Sub "Who I Am" wrote: Hello: One column is dedicated to phone number. When I copy-paste phone number to it, I want all phone numbers in the same format like 999-999-9999. I don't want to see 999.999.9999 or (999) 999-9999 or (999)-999-9999 I want this event gets triggered only after I paste the phone number into a cell in the column and hit RETURN Could you help? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Event Only Affects One Column
It only works if I type 9999999999. If I copy 999.999.9999 to a cell,
it does not work. So I think I must make all types of number strings, like 999.999.9999, (999)-999-9999, to become no space (trim it), no punctuation. Then apply your code. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Event Only Affects One Column
I assumed data was input into a GENERAL/TEXT format field.
"Who I Am" wrote: It only works if I type 9999999999. If I copy 999.999.9999 to a cell, it does not work. So I think I must make all types of number strings, like 999.999.9999, (999)-999-9999, to become no space (trim it), no punctuation. Then apply your code. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Event Only Affects One Column
Copying 999.999.9999 from/to NUMBER cell works OK for me (XL2003)
"Toppers" wrote: I assumed data was input into a GENERAL/TEXT format field. "Who I Am" wrote: It only works if I type 9999999999. If I copy 999.999.9999 to a cell, it does not work. So I think I must make all types of number strings, like 999.999.9999, (999)-999-9999, to become no space (trim it), no punctuation. Then apply your code. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I format one cell so that it affects another cell? | Excel Worksheet Functions | |||
Inserting a new column affects conditional formatting | New Users to Excel | |||
One workbook affects another | Excel Discussion (Misc queries) | |||
Excel - unHiding a column affects columns in other worksheets? | Excel Worksheet Functions | |||
How to trap delete row event and hide column event? | Excel Programming |