Opening grouped cells automatically

[Ben01]

Hi guys,

I need some help.
I have a couple of groups of columns grouped, i.e. separate groupings of 5 columns.
Certain cells within various groups have hyperlinks that are linked to certain cells that are within other groups.
If a group is closed (minimized with the '+' sign displayed) and I click on a hyperlink to a cell within this group the cell does not display as it is obviously hidden due to the group being closed.
I then have to click the plus sign at the top to display the cell I was taken to with the hyperlink. This sometimes leads me to losing my place and not realizing where the link has taken me to.
Is there a way to automatically ungroup the columns I have been taken to by the hyperlink so that the cell is selected and displayed and not hidden?
Thanks for the help guys.

 

[Gary McMaster]

Hi Ben,

Give the following sample a try in the indicated event.

Hope it helps.

Gary

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

If Range(Target.SubAddress).EntireRow.Hidden Then
   Range(Target.SubAddress).EntireRow.ShowDetail = True
End If

End Sub

 

[Ben01]

Hi Gary, Can I have a little more help than this please? Sorry I am no excel expert... Is this macro or vb stuff? or can I do this with the insert function... Thanks

 

[Gary McMaster]

Right click on one of the sheet tabs and select "View Code" from the popup menu.

In the upper left (usual location) of the VBA IDE, double click on "ThisWorkbook" (in the "Project ..." window)

Copy and paste the code into the code window (usually at the right) which has two dropdown boxes at the top. The drop down box on the left should say "Workbook"

Close the VBA IDE and the code should run every time you click a hyperlink on any worksheet.

Gary

 

[Ben01]

Thanks so much Gary. Got it to work. I swapped the 'EntireRow' to 'EntireColumn' as it was the columns I wanted to open up, not rows, but otherwise perfect step by step instructions.

 

[Gary McMaster]

Glad it works. Thanks for the feedback.

Gary

 

[Ben01]

Hey Gary, I am now having a problem with the workbook since adding in this rule... Within the workbook I have other cells with hyperlinks to external websites, that obviously when clicking on them the website opens in the default browser...

Now though when I click any of these a message comes up saying
"Run-time error '1004':Method 'Range' of object '_Global' failed"
with options to press 'end' 'debug' or 'help' beneath.

I press debug and this opens the Visual basic window and the code I inserted with the following line highlighted in yellow:

"If Range(Target.SubAddress).EntireColumn.Hidden Then"

If I keep this window open then I can click any of the website hyperlinks then they open correctly, but if I shut it, then the message appears again and so on... Is there a way of correcting the code or anything so I do not need to debug?

 

[Gary McMaster]

First thing to try is to add the line shown in red below.

Gary

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

[COLOR=Red]If Target.SubAddress = "" Then Exit Sub[/COLOR]

If Range(Target.SubAddress).EntireRow.Hidden Then
   Range(Target.SubAddress).EntireRow.ShowDetail = True
End If

End Sub

 

[Ben01]

Work's perfectly, thank you very much!

 

[Gary McMaster]

You could also do something like this (2 lines in red):

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

[COLOR=Red]On Error Resume Next[/COLOR]

If Range(Target.SubAddress).EntireRow.Hidden Then
   Range(Target.SubAddress).EntireRow.ShowDetail = True
End If

[COLOR=Red]On Error GoTo 0[/COLOR]

End Sub

This ignores the error and keeps on running. However, many/most people will argue that it is better to prevent the error from happening rather than letting it occur and then ignoring it as the above sample does.

Gary