Excel VBA how to copy information from one cell to another between work sheets using a hyperlink

Question

I have a calendar that I am trying to create that will highlight any days that have activities on them. There is a yearly view which shows all of the months in a given year. In this view I have set up that if there is a scheduled activity on any day that that day will be highlighted. What I'm trying to accomplish is if you select a date from this view it will take you to another sheet that will have a more detailed schedule for the week in question. I have all of the tools set up to show the details for that date, however my problem is pulling that date from the yearly calendar sheet and having it input that to a given cell in the weekly sheet.

This is the code that i have been using if is for one cell hyper-linked case but I can't get it to work generically with any hyper-linked cell.

Dim GSourceCell As String

Private Sub workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

If Sh.Name = "Year" Then
   If GSourceCell = "C8" Then
    Sheets("Current Week").Range("G4").Value = Sheets("Year").Range("C8").Value
   Else
       Sheets("Current Week").Range("G4").Value = ""
   End If
End If
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

   If Sh.Name = "Year" Then
      'Capture last active cell on Hyperlinks worksheet and store in global variable
      GSourceCell = Target.address(False, False)
  End If

End Sub

This is what I thought would work for a generic case.

Dim GSourceCell As String

Private Sub workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

If Sh.Name = "Year" Then
   If GSourceCell = Sheets("Year").Cells(ActiveCell.Row, ActiveCell.Column).address Then
    Sheets("Current Week").Range("G4").Value = Sheets("Year").Range(Cells(ActiveCell.Row, ActiveCell.Column)).Value
   Else
       Sheets("Current Week").Range("G4").Value = ""
   End If
End If
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

   If Sh.Name = "Year" Then
      'Capture last active cell on Hyperlinks worksheet and store in global variable
      GSourceCell = Target.address(False, False)
  End If

End Sub    

Any help on this would be much appreciated. Thank you

Answer 1

A few thoughts:

If you move your events from the ThiwWorkbook module and into the "Year" sheet module, you have a little more control over when the event fires. (And you can eliminate one of your If statements.)

Since you're already using a Worksheet_SelectionChange event, why not just use that sub to check if the new selection has a hyperlink? You could then do all your calculations in that same sub. For example:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'This code is located in the sheet module for your "Year" sheet.
If Target.Hyperlinks.Count = 1 Then
    'If the new selection is a hyperlinked cell, put the cell's value in G4 on the _
    'Current Week sheet.
    Sheets("Current Week").Range("G4").Value = Target.Value
Else
   'If the cell isn't hyperlinked, put nothing in G4.
   Sheets("Current Week").Range("G4").Value = vbNullString
End If

End Sub