如何通过使用超链接（或通过将宏分配给超链接）在Excel中扩展组 [英] How to expand a group in Excel by using Hyperlink(or by maybe assigning Macro to Hyperlink)

问题描述

我的表格顶部有一张表格，这个表格有不同的部分名称。
当我点击它们时，我想插入一个超级链接到这些部分名称去打开它的组。

I have a table at the top of my sheet and this table has a different section names. I'd like to insert a hyperlink to these section names to go and open it's group below when I click them.

请参阅我的观点表和部分作为默认（折叠）

我可以创建一个宏：

Expands all groups
Goes to the Section that I clicked,
Collapses all groups
Only opens the group on active cell, 

但是，将这个宏分配到〜20个不同的部分会增加文件大小。

But assigning this macro to ~20 different sections increases the file size.

经过一些搜索，我在这里找到了这个： Excel：将宏分配给超链接？所以也许有一种方法来连接这两种方法？

After some search I found this on SO: Excel: Assign a macro to a hyperlink? So maybe there is a way to connect this two method?

如何解决？

推荐答案

使用组表格和您需要的任何汇总创建主表。随后的工作表可以包含所有部分数据。这有更多的可扩展性的附加益处。

I'd suggest creating a master sheet with the "group" table and any rollups you need. The subsequent sheets could have all the "section" data on them. This has the added benefit of being more scaleable.

是否必须将所有信息放在同一张表上？这几乎是为什么Excel有多张表。使用多张表格也可以使用标准的超链接。

Is it strictly necessary to have all the information on the same sheet? This is pretty much why Excel has multiple sheets. Using multiple sheets would also allow you to use standard hyperlinks.

但是，如果您希望某些VBA让您更接近，请考虑以下代码。这将从活动单元格中获取值，然后使用该值搜索下一个单元格。如果找到的单元格的部分折叠，它将展开它，反之亦然。

However, if you would like some VBA to get you closer, consider the code below. This grabs the value form the active cell, then searches for the next cell with that value. If the section with the found cell is collapsed, it expands it and visa versa.

Sub OpenSection()

Dim x As String
x = ActiveCell.Value

Dim y As String
y = Cells.Find(What:=(x), After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Address
'Range("b1").Value = y

With ActiveSheet
    With .Range(y).EntireRow
        If .ShowDetail = False Then
            .ShowDetail = True
        Else
            .ShowDetail = False
        End If
    End With
End With
End Sub

这篇关于如何通过使用超链接（或通过将宏分配给超链接）在Excel中扩展组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！