"If Target.NumberFormat = " not working?

LGFN1

Board Regular
Joined
Jun 25, 2014
Messages
63
How can I get this to work:

I have two kinds of hyperlinks on a sheet and I need one kind to just follow the link, but the second kind should execute a macro. The second kind of link only has a number format of "m/d/yyyy h:mm", and is only in Column A. I got stuck by the number format:

Code:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.NumberFormat = "m/d/yyyy h:mm" Then
        MsgBox ActiveCell.Value
    End If
End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Target is a Hyperlink object and a Hyperlink doesn't have a NumberFormat property.
To access the NumberFormat of the cell that holds the hyperlink, you can use the Range Property...

Code:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range(1).NumberFormat = "m/d/yyyy h:mm" Then
        MsgBox Target.Range(1).Value
    End If
End Sub

... but the second kind should execute a macro.

Be aware that the FollowHyperlink event code is triggered after the hyperlink jump occurs. If you want the second kind of Hyperlink to execute a macro instead of following the hyperlink, you'll need a different approach like...

1. Have the FollowHyperlink code go back to the cell that holds the hyperlink.

2. Change the Hyperlink destination to be the same as the cell that holds the hyperlink (a dummy hyperlink). Then have the FollowHyperlink code execute the macro.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top