VBA - 工作表列表（超链接） [英] VBA - List of sheets (hyperlinked)

问题描述

我有一个Excel-Workbook。在本工作簿中，通过VBA创建一个新的工作表。

这个工作簿越多的混乱就是它，因为我必须滚动很长时间才能到达任何工作表在中间。

我想创建一个概览表

• 列表的名称列于AND


• 表格的名称必须是超链接。

我的代码根本不起作用 -
BTW，我必须使用Excel 2003

这是我有的：

  Sub GetHyperlinks（）
 Dim ws As Worksheet 
 Dim i As Integer 
 
i = 4 
 
 ActiveWorkbook.Sheets（overview）。单元格（i，1）。选择
 
对于工作表中的每个ws 
 ActiveWorkbook.Sheets（overwies）。 Hyperlinks.Add _ 
 Ancor：= Selection，_ 
地址：=，_ 
 SubAddress：='ws.name'，_ 
 TextToDisplay：=' ws.name'
 
i = i + 1 
下一个ws 
 End Sub 
  

解决方案

改变你的代码 - 现在可以使用：

  Sub GetHyperlinks（）
 Dim ws As Worksheet 
 Dim i As Integer 
 
i = 4 
 
对于每个ws在ThisWorkbook.Worksheets 
 ActiveWorkbook.Sheets（overview）。Hyperlinks.Add _ 
 Anchor：= ActiveWorkbook.Sheets（overview）。单元格（i，1），_ 
地址：=，_ 
 SubAddress：='& ws.Name& '！A1，_ 
 TextToDisplay：= ws.Name 
 
i = i + 1 
下一个ws 
 End Sub 
  

I have an Excel-Workbook. In this workbook a new sheet is created via VBA.

The more sheets this workbook has the more confusing is it, because I have to scroll a long time to reach any sheet in the middle.

I want to create an overview-sheet

• in which the names of the sheets are listed AND
• the name of the sheets have to be hyperlinks.

My code doesn't work at all - BTW, I have to work with Excel 2003

Here's what I have:

Sub GetHyperlinks()
    Dim ws As Worksheet
    Dim i As Integer

    i = 4

    ActiveWorkbook.Sheets("overview").Cells(i, 1).Select

    For Each ws In Worksheets
        ActiveWorkbook.Sheets("overwies").Hyperlinks.Add _
        Ancor:=Selection, _
        Address:="", _
        SubAddress:="'ws.name'", _
        TextToDisplay:="'ws.name'"

        i = i + 1
    Next ws
End Sub

解决方案

Altered your code a bit - this now works:

Sub GetHyperlinks()
    Dim ws As Worksheet
    Dim i As Integer

    i = 4

    For Each ws In ThisWorkbook.Worksheets
        ActiveWorkbook.Sheets("overview").Hyperlinks.Add _
        Anchor:=ActiveWorkbook.Sheets("overview").Cells(i, 1), _
        Address:="", _
        SubAddress:="'" & ws.Name & "'!A1", _
        TextToDisplay:=ws.Name

        i = i + 1
    Next ws
End Sub

这篇关于VBA - 工作表列表（超链接）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！