HOW TO: If any cell on sheet is selected (jumped to) UnHide?

[Silverjman]

Hello All,

FYI: I have a couple sheets that have 12 to 15 groups of grouped columns, these have replaced 24 to 30 separate sheets.

Other sheets, say the Cashflow sheet, link to the two sheets with loads of groupings. I/the user often double clicks or
"ctrl-[" to jump from the Cashflow sheet to the aforementioned only to have those sheet grouped and the cell selected hidden.

Is there a single code snippet that if applied to the entire sheet would automatically ungroup the group of the cell that is being jumped to?

IF cell is selected expand grouping (in this case a Named Range)? Coding with Named Ranges is key for me here.


The below is just OTHER CODE that I have cobbled together and assigned to buttons on the respective sheets to pop open the groupings and give the user a similar experience to individual sheet tabs.

Sub UnCollapseAssetsDebt() 

Set Debt = Range("Assets.Debt")   'Assets.Debt = Sheet's Name . Named Range

If Debt.EntireColumn.Hidden = True Then
   Debt.EntireColumn.Hidden = False
End If

Debt(10, 1).Select

End Sub

Sub CollapseAssetsDebt()

Set Debt = Range("Assets.Debt")

If Debt.EntireColumn.Hidden = False Then
   Debt.EntireColumn.Hidden = True
End If

Range("Assets.Assets")(10, 1).Select  'This just send the user back to the far left of the sheet

End Sub

 

[Rhodie72]

Whilst I'm at a loss to help you solve this "group" thing as i fail to understand what you mean by group, I have condensed your code a bit to help where i can.

Sub UnCollapseAssetsDebt()

    Set Debt = Range("Assets.Debt")   'Assets.Debt = Sheet's Name . Named Range

    If Debt.EntireColumn.Hidden = True Then Debt.EntireColumn.Hidden = False

    Debt(10, 1).Select

End Sub

Sub CollapseAssetsDebt()

    Set Debt = Range("Assets.Debt")
    
    If Debt.EntireColumn.Hidden = False Then Debt.EntireColumn.Hidden = True
    
    Range("Assets.Assets")(10, 1).Select  'This just send the user back to the far left of the sheet

End Sub

 

[Silverjman]

THANKS! I am loathe to be one of those people that writes horrible code for years, and get cursed, and I knew mine was lacking.

When I refer to groups I mean Alt-A-G, just Group and Ungroup in the Data tab.

This is what it looks link when the user Ctrl-G's Assets!:ABQ10 (or doubleclicks a link to said cell). I am wondering if there is a way to automatically pop this group open when the jump happens.

I was losing my frozen panes when I ran the macro so below is my update to remedy that. I need to fix this to display the cell it brings the user back to the Selected cell even after it's open, i.e. Hidden=FALSE, for some reason this works for other areas but not this one, i.e. frozen panes is forcing the user to hit right arrow to display the cell.

No help expected on this one just thought I would jot it down.

Sub UnCollapseAssetsDebt()

Application.ScreenUpdating = False

Set Debt = Range("Assets.Debt")

If Debt.EntireColumn.Hidden = True Then
   Debt.EntireColumn.Hidden = False
End If


Debt(10, 1).Select

End Sub

Sub CollapseAssetsDebt()

Set Debt = Range("Assets.Debt")

If Debt.EntireColumn.Hidden = False Then
   Debt.EntireColumn.Hidden = True
End If


Range("Assets.Assets")(9, 15).Select
     
    With ActiveWindow
         '// Clear any existing...
        .FreezePanes = False
        .FreezePanes = True
    End With


Range("Assets.Assets")(10, 1).Select

End Sub

 

[Rhodie72]

I'm sure you can figure out how to "open the group" if you put your mind to it <evil grin="">(evil grin)<evil grin="">

As for your code, unless you intend on writing more in the IF statements, use just one line to save on typing and code speed.

Sub UnCollapseAssetsDebt()

Application.ScreenUpdating = False

Set Debt = Range("Assets.Debt")

If Debt.EntireColumn.Hidden = True Then Debt.EntireColumn.Hidden = False

Debt(10, 1).Select

End Sub

Sub CollapseAssetsDebt()

Set Debt = Range("Assets.Debt")

If Debt.EntireColumn.Hidden = False Then Debt.EntireColumn.Hidden = True

Range("Assets.Assets")(9, 15).Select
     
    With ActiveWindow
        '// Clear any existing...
        .FreezePanes = False
        .FreezePanes = True
    End With


Range("Assets.Assets")(10, 1).Select

End Sub

</evil></evil>