VBA Automatic formatting

[keranali]

Hi everyone, how to type in a cell and on pressing enter the cell formats its self

example

Col J you can enter a name john.doe and the cell takes the name and now shows john.doe@xxxxx.com, www.xxxxx.com

Thanks
K

 

[lenze]

Something like this??

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target = Target & "@yahoo.com"
Application.EnableEvents = True
End Sub

lenze

 

[keranali]

Thanks for your reply, the macro works great however it's applying the code to all cells how can I restrict this code to one column.

Thanks
K

 

[Peter_SSs]

If that code does the basic job you want then I would suggest a couple of other changes as well as restricting it to one column.

As the code stands now

a) If you change more than one cell at a time (eg select 2 cells and press 'Delete') the code will error out.

b) If you delete a cell it will remain with "@yahoo.com" in it which I doubt you want.

If you have found that the code has errored out, you need to make sure your 'Events' are not disabled. To ensure they are still enabled, in the VBA window, ensure the Immediate Window is visible (View|Immediate Window) and then on a new line in the Immediate Window, type
Application.EnableEvents=True and press Enter


Here is some code that copes with the issues mentioned above and only acts on column J.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> Changed <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br><br>    <SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Columns("J"))<br><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Changed<br>            <SPAN style="color:#00007F">If</SPAN> Len(c.Value) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                c.Value = c.Value & "@yahoo.com"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[keranali]

This was exactly what I am looking for yes the previous code did error out disabling the sheet, thanks for the heads up, also by changing this part

Set Changed = Intersect(Target, Columns("J"))
c.Value = c.Value & "@yahoo.com"

I can adjust the column and email address thanks very much for your help guys.

K

 

[keranali]

Hi Peter I hope all is well, can I use your code over again, but this time allowing the information to fall to the front of the data which has already been inserted. Now before we were using data that had to be entered before the change, now as the data is there how can the change be implemented.
example

Previous layout

XXXX@yahoo.com

New layout

@yahoo.comXXXXXX

I am leaning towards the code

  If Len(C.Value) > 0 Then
                C.Value = C.Value & "@yahoo.com"[\code]
which I believe has something to do with the layout. also what is ment by "C as range"

Thanks
Keran

 

[Peter_SSs]

I don't understand just what you are asking this time. Can you try to explain it a different way and perhaps give a few more examples?

 

[keranali]

Hello Peter thanks for your help I got a break through with the problem mentioned earlier

Dim Lastrow As Long, i As Long
Lastrow = Cells(Rows.Count, 11).End(xlUp).Row
For K = 2 To Lastrow
    If Not IsEmpty(Cells(K, 11).Value) Then Cells(K, 11).Value = "Tel.:         " & Cells(K, 11).Value
Next K


Lastrow = Cells(Rows.Count, 12).End(xlUp).Row
For L = 2 To Lastrow
    If Not IsEmpty(Cells(L, 12).Value) Then Cells(L, 12).Value = "Ext.:          " & Cells(L, 12).Value
Next L

Lastrow = Cells(Rows.Count, 14).End(xlUp).Row
For N = 2 To Lastrow
    If Not IsEmpty(Cells(N, 14).Value) Then Cells(N, 14).Value = "Cel.:         " & Cells(N, 14).Value
Next N


Lastrow = Cells(Rows.Count, 13).End(xlUp).Row
For i = 2 To Lastrow
    If Not IsEmpty(Cells(i, 13).Value) Then Cells(i, 13).Value = "Fax:         " & Cells(i, 13).Value
Next i


         Case Else
            Exit Sub
    End Select
End Sub

Thanks for looking out.

K