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

 

[Guest]

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]

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

 

[Guest]

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]

Hi Dave,

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

Best regards,
Pieter-Jan :