Paste Email Address without Hyperlink property

[Anne Troy]

I want a user to be able to paste email addresses into certain cells, let's say D3, and D5 through D7, and when they do, the email address is pasted, the font is dropped to lower case, and the hyperlink property does NOT exist.

I am already using the following code, so I can alter it for the lowercase portion,

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    On Error GoTo ErrHandler:
    If Not Application.Intersect(Me.Range("C10:D10"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            'Target.Value = StrConv(Target.Text, vbLowerCase)
            'Target.Value = StrConv(Target.Text, vbUpperCase)
            Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If
ErrHandler:
    Application.EnableEvents = True
End Sub[code]


and I know how to find code to remove hyperlinks in EXISTING data, but not in new data. 

Code I've tried doesn't work. I'm sorry I don't have that code anymore, so I can't show you what didn't work.

Bottom line, can you alter the code above to work for me? Thanks so much in advance!!

 

[VoG]

Maybe

With Target
    .Value = StrConv(Target.Text, vbProperCase)
    .Hyperlinks.Delete
End With

 

[Anne Troy]

Hate to ask... where do I place that?

 

[VoG]

Try like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
    Exit Sub
End If
On Error GoTo ErrHandler:
If Not Application.Intersect(Me.Range("C10:D10"), Target) Is Nothing Then
    If IsNumeric(Target.Value) = False Then
        Application.EnableEvents = False
        'Target.Value = StrConv(Target.Text, vbLowerCase)
        'Target.Value = StrConv(Target.Text, vbUpperCase)
        With Target
            .Value = StrConv(Target.Text, vbProperCase)
            .Hyperlinks.Delete
        End With
        Application.EnableEvents = True
    End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Anne Troy]

Thanks! Will try at work in a couple hours.

 

[Anne Troy]

That works great! Thanks so much. I'm about to post an issue with the change event on that same code.