在Excel VBA中创建自我更新的目录 [英] Creating a table of contents in Excel VBA which self updates

问题描述

我是VBA的新手，我正在Excel中从事一个简单的项目.我有一份设备清单，每件都分成不同的工作表.第一个工作表是一个目录，其中包含所有设备的列表.单击一个将用户带到该特定设备的工作表.目前，我已将宏绑定到名为New_Entry的按钮上，该按钮可根据模板创建新的工作表:

I am new to VBA and I am working on a simple project in Excel. I have an inventory of equipment, each piece separated into a different worksheet. The very first worksheet is a table of contents with a list of all the equipment. Clicking on one takes the user to the worksheet for that specific piece of equipment. Currently, I have tied a macro to a button called New_Entry which creates a new sheet off of a template:

Sub New_Entry()
    Sheets.Add Type:= _
        "C:\Users\MyName\AppData\Roaming\Microsoft\Templates\Archive_Entry.xltx"
    ActiveSheet.Move After:=Worksheets(Worksheets.Count)
End Sub

我想知道是否可以通过按钮添加的每个新工作表中的单元格B2(即该设备的名称)的内容自动更新目录.这是用于创建超链接的录制宏:

I am wondering if it is possible for the table of contents to be automatically updated with the content of, say, cell B2 (i.e. the name of that piece of equipment) of every new worksheet added via the button. This is the recorded macro for creating a hyperlink:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'Sheet1 (24)'!B1", TextToDisplay:="Sheet1 (24)!B1"

推荐答案

为了获得结果，您可以应用我在评论中描述的解决方案，而不是对 Sheet 索引应用 Worksheets(Worksheets.Count)，即对应于最后一个 Worksheet 的索引，如以下VBA代码片段所示:

In order to achieve the result, you can apply the solution I've described in my comment but instead of hard-coding the Sheet index apply Worksheets(Worksheets.Count) , i.e. the index corresponding to the last Worksheet as shown in the the following VBA code snippet:

Sub New_Entry()
    Sheets.Add Type:= _
    "C:\Users\MyName\AppData\Roaming\Microsoft\Templates\Archive_Entry.xltx"

    ActiveSheet.Move After:=Worksheets(Worksheets.Count)

    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'Sheet1 (Worksheets(Worksheets.Count))'!B1", TextToDisplay:="Sheet1 (Worksheets(Worksheets.Count))!B1"
End Sub

希望这会有所帮助

这篇关于在Excel VBA中创建自我更新的目录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！