Hyperlink to call multiple macros Excel 2010

[Otisair]

Hello,

I have 3 columns that contain hyperlinks (to self (ie. cell A1 hyperlink points to A1 and titled View)). Each column's hyperlink should call a different macro to goto a report page to look at a dated report (pic below). When I run each macro individually, they run without error. However, when I try to use a If...ElseIf....ElseIf....Else type entry, nothing appears to happen. The If....Else code is entered on the reports spreadsheet itself and not into a module.

Does anyone see anything obvious as to why this wouldn't call the macro correctly? If I select, for example, the 3rd "view" down in the first column containing hyperlinks, I should be taken to the Load Mgmt reports page where I have a vlookup table setup to display the results based on the Request ID (far left). Again, it works perfectly if I manually run the macro.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
      
    If Target.Range.Address = "$N$5:$N$17" Then
       Call lmreps
    ElseIf Target.Range.Address = "$O$5:$O$17" Then
       Call rlreps
    ElseIf Target.Range.Address = "$P$5:$P$17" Then
       Call viewers
    End If
End Sub

Many Thanks,

Shane

 

[Norie]

Shane

Have you checked what the actual address of the range the hyperlink being clicked is?

I'm pretty sure if you click the hyperlink in O10 the address isn't going to be '$O$5:$O$17'.

 

[Otisair]

Hi Norie,

Thanks for the reply.

Each hyperlink in the columns with "VIEW" is actually pointing back to itself. Cell O10 hyperlink reference is O10 and the name is View. I hyper-linked them individually because in the macros, I have it grab the value of where the row intersects with column A (ie. O10's Resource ID (in the picture above) is 894. I use 894 as the vlookup value for that dates report.

Perhaps I'll need to rethink how I'm doing this. Each row entry has three reports that took place on that particular date. I want to allow the user to select either of the three reports to view the postings from that date.

Thanks,
Shane

 

[Otisair]

When I excluded the full range, all works great. This seems like an awful lot of code for a little thing. Can I consolidate this? Thanks.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Application.ScreenUpdating = False
      
    If Target.Range.Address = "$N$5" Then
       Call lmreps
    ElseIf Target.Range.Address = "$N$6" Then
       Call lmreps
    ElseIf Target.Range.Address = "$N$7" Then
       Call lmreps
    ElseIf Target.Range.Address = "$N$8" Then
       Call lmreps
    ElseIf Target.Range.Address = "$N$9" Then
       Call lmreps
    ElseIf Target.Range.Address = "$N$10" Then
       Call lmreps
    ElseIf Target.Range.Address = "$N$11" Then
       Call lmreps
    ElseIf Target.Range.Address = "$N$12" Then
       Call lmreps
    ElseIf Target.Range.Address = "$N$13" Then
       Call lmreps
    ElseIf Target.Range.Address = "$N$14" Then
       Call lmreps
    ElseIf Target.Range.Address = "$N$15" Then
       Call lmreps
    ElseIf Target.Range.Address = "$N$16" Then
       Call lmreps
    ElseIf Target.Range.Address = "$N$17" Then
       Call lmreps
    ElseIf Target.Range.Address = "$O$5" Then
       Call rlreps
    ElseIf Target.Range.Address = "$O$6" Then
       Call rlreps
    ElseIf Target.Range.Address = "$O$7" Then
       Call rlreps
    ElseIf Target.Range.Address = "$O$8" Then
       Call rlreps
    ElseIf Target.Range.Address = "$O$9" Then
       Call rlreps
    ElseIf Target.Range.Address = "$O$10" Then
       Call rlreps
    ElseIf Target.Range.Address = "$O$11" Then
       Call rlreps
    ElseIf Target.Range.Address = "$O$12" Then
       Call rlreps
    ElseIf Target.Range.Address = "$O$13" Then
       Call rlreps
    ElseIf Target.Range.Address = "$O$14" Then
       Call rlreps
    ElseIf Target.Range.Address = "$O$15" Then
       Call rlreps
    ElseIf Target.Range.Address = "$O$16" Then
       Call rlreps
    ElseIf Target.Range.Address = "$O$17" Then
       Call rlreps
    ElseIf Target.Range.Address = "$P$5" Then
       Call viewers
    ElseIf Target.Range.Address = "$P$6" Then
       Call viewers
    ElseIf Target.Range.Address = "$P$7" Then
       Call viewers
    ElseIf Target.Range.Address = "$P$8" Then
       Call viewers
    ElseIf Target.Range.Address = "$P$9" Then
       Call viewers
    ElseIf Target.Range.Address = "$P$10" Then
       Call viewers
    ElseIf Target.Range.Address = "$P$11" Then
       Call viewers
    ElseIf Target.Range.Address = "$P$12" Then
       Call viewers
    ElseIf Target.Range.Address = "$P$13" Then
       Call viewers
    ElseIf Target.Range.Address = "$P$14" Then
       Call viewers
    ElseIf Target.Range.Address = "$P$15" Then
       Call viewers
    ElseIf Target.Range.Address = "$P$16" Then
       Call viewers
    ElseIf Target.Range.Address = "$P$17" Then
       Call viewers
      End If
        Application.ScreenUpdating = True
End Sub