3

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.

Please Refer to the view of my table and sections as default (Collapsed)

I could create a macro which:

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

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

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?

How this can be solved?

Community
  • 1
  • 1
Mertinc
  • 793
  • 2
  • 13
  • 27

1 Answers1

2

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.

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.

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
sadtank
  • 320
  • 1
  • 12
  • Even if I divide them into different sheets, what's your solution I didn't understand it clearly? And yes they need to be on the same sheet because workbook already has enough sheets, this one is just as a reference. – Mertinc Apr 12 '17 at 06:14
  • I was thinking about giving groups 1, 2, and 3 their own sheet, then using normal links to those sections, each having their own sheet. That way adding more sections would just be adding a normal link and a normal sheet. No funny business with collapsing/expanding needed. – sadtank Apr 12 '17 at 16:33
  • That said, I'm updating my original answer to include some VBA that might help get you closer. – sadtank Apr 12 '17 at 16:34
  • After a month, I'd like to thank you for your great suggestion. By the help of your code and the one I mentioned in my question which is:[http://stackoverflow.com/questions/4465017/excel-assign-a-macro-to-a-hyperlink] I solved the problem. Can you please develop your answer so that I can approve this as an answer. After I wrote this in my Worksheet Object page,`Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Run ("OpenSection") End Sub` and added your code in the module everything works like charm. In your code, forget about X and write only: `Dim y As String y = ActiveCell.Address` – Mertinc May 11 '17 at 03:27
  • What happens here is, after you assign `HyperLink` to anywhere in your sheet, when you click the HyperLink it firstly goes to cell that you Hyperlinked. Then, it runs our macro which is `OpenSection` And what our macro does is, If first saves `Activecell.Address` which is the X,Y coordinates of the Activecell (and this Activecell is the one of our Sections - and it has it's own group) then it selects entire row by `.Range(y).EntireRow` and if it has any group below it, it changes the `.ShowDetail = False` to `True` and da-da-da-dam: our section group is expanded. – Mertinc May 11 '17 at 03:36