Excel How to programmatically hide columns while keeping navigation buttons in view at top

Question

I have a large Excel sheet with some top-level navigation buttons. I'd prefer that when I click a button, the relevant data appears without having to scroll by either jumping to the columns it relates to or hiding the columns to its left.

However, because those columns also contain data, it is possible for them to be collapsed or concealed. The buttons will therefore disappear from the display or get collapsed (hidden) with the columns in which they are located if a user selects a button to move to a section that is far to the right.

What's the best approach here? 1) Should I collapse the columns and relocate every button above the currently displayed section? 2) Can I bring the buttons with me and link to the appropriate columns? 3. Is it possible to freeze a group or section at the top so that it remains visible even when columns are collapsed? 3) Is there anything else you want to add?

I have a lot of experience with Access and VBA, but not as much with Excel. Therefore, there are probably numerous ways to address this when you have a wide sheet. We would appreciate any assistance.

Answer 1

Obviously, this does not help with the out-of-view, but it will help with the collapsing columns. Buttons give the option of Don't move or size with cells.

To collapse the columns the following should work

 Sub hideAtoC
     column_hider "A", "C"
 End Sub

 Sub hideDtoR
     column_hider "D", "R"
 End Sub  

 Sub column_hider(first_column,last_column)
     Sheet1.Columns.Hidden = False
     Worksheets("YOUR WORKSHEET NAME").Range(first_column & ":" & last_column).EntireColumn.Hidden = True
 End

Then, all you have to do is connect your buttons to the relevant module, such as hideAtoC or hideDtoR.

Depending on how many controls there are, moving them becomes more difficult, but this will work with some obvious modifications.

Sub moveWithMe
   With Worksheets("YOUR WORKSHEET NAME")
       .Cells(1,"BA").Select
       .Shapes(1).Left = ActiveCell.Left
   End With
End Sub

This is a pretty basic illustration. The scenario appears more involved and would likely require an offset for button width because all it does is select a cell before aligning the button with it. This is a conceptual loop, but you get the idea (although it will suffer if the cell is on the leftmost visible column).

Dim offset As Long
offset = ActiveCell.Left
For i = 1 To Sheet1.Shapes.Count
     Sheet1.Shapes(i).Left = offset - (Sheet1.Shapes(i).Width / 2)
     offset = offset + Sheet1.Shapes(i).Width + 10
Next i