使活动单元格成为新创建的选项卡的超链接

2 回答

• 1

  创建超链接时， Address 参数对于现有工作簿中的位置应为空白，并且 SubAddress 参数可以使用 MySheetName 变量而不是无效 Sheets("Index").ActiveCell.Text 创建（无效因为 ActiveCell 是活动单元格 - 它不是a的属性 Worksheet 对象） .

  您的代码的重构版本将是：

  Sub CopyIIDTemplate()
      Dim MySheetName As String
      MySheetName = InputBox("Please type your first and last names.")
  
      'Make a copy of the "Template" sheet and place it after the last worksheet
      Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
  
      'Use a "With" block to make typing easier (i.e. we can use
      '".xxx" instead of "Worksheets(Worksheets.Count).xxx" everywhere
      'within the block
      '
      'Worksheets.Count gives the number of worksheets in the workbook.
      'Worksheets(Worksheets.Count) therefore gives the last worksheet
      'in the workbook, which is where we placed the copy of "Template".
      With Worksheets(Worksheets.Count)
          'Set this sheet's name
          .Name = MySheetName
          'Set cell B1's value to be the person's name
          .Range("B1").Value = MySheetName
      End With
  
      'Use another "With" block to save typing.  This time we are
      'using the cell one row below the last non-empty cell above cell
      'B51 on the worksheet called "Index".
      '
      'Note: Using Range("B" & Worksheets("Index").Rows.Count) instead of
      'Range("B51") may be better - that would find the last non-empty cell
      'in the entire column B.
      '
      With Worksheets("Index").Range("B51").End(xlUp).Offset(1)
          'Set the cell's value to be the person's name
          .Value = MySheetName
  
          'Add the hyperlink
          '
          'Anchor (i.e. where to place the hyperlink):
          '".Cells(1,1)" when applied to a range will give the top-left
          'corner of the range.  The range that we are applying it to is
          'a single cell, so this will simply point to that same cell.
          'It would have been nice to be able to just say "." to point to
          'the cell, but that would undoubtedly not work.
          '
          'Address (i.e. file/url to refer to):
          'Just use "" for a reference to the existing workbook
          '
          'SubAddress (i.e. location within the Address):
          'Need to create an address similar to "'Ian'!A4"
          'Wrap the person's name in "'" characters, so that embedded
          'spaces don't cause issues, and then stick a "!A4" at the end.
          '
          'Note: If the person's name contains any "'" characters (e.g.
          '"Antonio D'amore") the simplified version used here won't work
          'because "'Antonio D'amore'!A4" actually needs to be
          '"'Antonio D''amore'!A4", but let's ignore that for now.
          '
          .Hyperlinks.Add Anchor:=.Cells(1, 1), _
                          Address:="", _
                          SubAddress:="'" & mySheetName & "'!A4"
      End With
  End Sub
  

  注意：如果您的任何员工的名称中包含 ' ，则子地址参数必须为 SubAddress:="'" & Replace(mySheetName, "'", "''") & "'!A4" .

  回复于 2024-05-19T04:33:54+08:00
• 0

  最后一行中有错误的引用：

  Sheets("Index").Hyperlinks.Add anchor:=Excel.Selection, Address:=ActiveCell.Value, SubAddress:="'" & Sheets("Index").ActiveCell.Text & "'!A4"
  

  将 Sheets("Index").ActiveCell 替换为 ActiveCell ，它应该可以工作 .

  回复于 2024-05-19T04:33:54+08:00