Replace text in code with text that is double-clicked

[cstuart79]

I have the following code used to open a new Outlook email message upon double-clicking the email address.

Private Sub Email__1_DblClick(Cancel As Integer)
Application.FollowHyperlink "mailto:" & "Email #1"
End Sub

I want to modify the code so that "Email #1" is replaced with the actual email address text that is being double-clicked. How can this be accomplished without having to create a code for each individual email address?

 

[HarleyQuinn]

Something like:

Private Sub Email__1_DblClick(Cancel As Integer)
Application.FollowHyperlink "mailto:" & Email__1.Text
End Sub

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[cstuart79]

tried your suggestion but keep getting run-time error 424: object required

 

[SkipVought]

Try this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    With Target.Parent
        .Hyperlinks.Add Anchor:=Target, _
            Address:=Target.Value, _
            TextToDisplay:=Target.Value
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cstuart79]

does this go in the "before update" event? i don't see a "before double click" event. also, do i replace any of the terms that you used such as "target" with field names or is it word for word code?

 

[SkipVought]

does this go in the "before update" event?

No. It is the Worksheet_BeforeDoubleClick event. Just paste into your Sheet Object Code Window.

do i replace any of the terms that you used such as "target" with field names or is it word for word code?

No. AS IS.





Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cstuart79]

when i put the following in "double click" event:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Target.Parent
.Hyperlinks.Add Anchor:=Target, _
Address:=Target.Value, _
TextToDisplay:=Target.Value
End With
End Sub


Private Sub Email__1_DblClick(Cancel As Integer)
Application.FollowHyperlink "mailto:" & Email_1.Text
End Sub

i get this error:

"The expression On Dbl Click you entered as the event property setting produced the following error: User-defined type not defined."
*The expression may not result in the name of a macro, the anme of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

 

[SkipVought]

You no longer need the Email__1_DblClick procedure.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Is this Excel or Access, since I notice that you have a similar question in an Access forum.

This forum does not routinely address MS Access questions, as there are a plethora of MS Access fora.

Coinsequently, I assumed Excel, which may be a error.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cstuart79]

sorry--this is an Access question, not excel. should i be posting elsewhere?

 

[SkipVought]

You already have posted thread702-1577879 in forum702.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}