Excel VBA如何使用超链接在工作表之间将信息从一个单元格复制到另一个单元格

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

任何有关这方面的帮助将非常感激。谢谢

1 个解决方案

#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.)

如果您将事件从ThiwWorkbook模块移动到“年份”工作表模块中,您可以更好地控制事件何时触发。 (你可以删除你的一个If语句。)

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:

由于您已经在使用Worksheet_SelectionChange事件,为什么不使用该子来检查新选择是否具有超链接?然后,您可以在同一个子计划中执行所有计算。例如:

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

#1