FollowHyperlink automatically

[Pieter-Jan]

I have seen that it is possible, with Visual Basic, to conditionally follow a hyperlink.

How should the code be if, f.e. at A5=100 (sum of other cells), automatically a hyperlink is followed? In my case a music fragment should be played.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
????
????
????
????
End Sub

What comes in the place of the questionmarks to get this function active?

Thanks.
Regards, Pieter-Jan

[DGolds]

I'm not sure if this is what you're after, but rather than trying to do this
through the Worksheet_FollowHyperlink event, you can place the following code
in the macro of your choice (in your case, perhaps in the Worksheet_Change
event when the value of a certain cell meets a certain condition) to
hyperlink to the desired file or website
whenever the macro is run:

ThisWorkbook.FollowHyperlink "AddressToHyperlinkTo", , True

e.g.:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$A$5" Then
If Target.Value = 100 Then
ThisWorkbook.FollowHyperlink "C:\MusicFragment.mp3", , True
End If
End If
End Sub

HTH,
Dave

"Pieter-Jan" wrote:

>
> I have seen that it is possible, with Visual Basic, to conditionally
> follow a hyperlink.
>
> How should the code be if, f.e. at A5=100 (sum of other cells),
> automatically a hyperlink is followed? In my case a music fragment
> should be played.
>
> Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
> ????
> ????
> ????
> ????
> End Sub
>
> What comes in the place of the questionmarks to get this function
> active?
>
> Thanks.
> Regards, Pieter-Jan
>
>
> --
> Pieter-Jan
> ------------------------------------------------------------------------
> Pieter-Jan's Profile: http://www.excelforum.com/member.php...o&userid=27954
> View this thread: http://www.excelforum.com/showthread...hreadid=474567
>
>

[Pieter-Jan]

Dave,

This is looking good to start with. Only one problem, the music is playing when I type "100" in "A5", but not when 100 is calculated in "A5". Can you solve this problem, or do I make a mistake?

Regards,
Pieter-Jan

[DGolds]

Pieter Jan,
Try pasting this code at the top of your sheet's code:

Option Explicit
Private intCounter As Integer

Private Sub Worksheet_Calculate()
On Error Resume Next
Static varVal As Variant
With Range("A5")
If .Value = 100 And (.Value <> varVal Or intCounter = 0) Then
ThisWorkbook.FollowHyperlink "C:\MusicFragment.mp3", , True
varVal = .Value
End If
End With
varVal = Range("A5").Value
intCounter = intCounter + 1
End Sub

You'll just need to change the name and path of the music file
HTH,
Dave

"Pieter-Jan" wrote:

>
> Dave,
>
> This is looking good to start with. Only one problem, the music is
> playing when I type "100" in "A5", but not when 100 is calculated in
> "A5". Can you solve this problem, or do I make a mistake?
>
> Regards,
> Pieter-Jan
>
>
> --
> Pieter-Jan
> ------------------------------------------------------------------------
> Pieter-Jan's Profile: http://www.excelforum.com/member.php...o&userid=27954
> View this thread: http://www.excelforum.com/showthread...hreadid=474567
>
>

[Pieter-Jan]

Hi Dave,

It's working great now!
Thank you very much.

Best regards,
Pieter-Jan