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

0 votes
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.
Nov 8 in Others by Kithuzzz
• 20,660 points
27 views

1 answer to this question.

0 votes

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
answered Nov 8 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

How to merge columns in Excel but keep data on other columns

VLOOKUP indeed can be used here, combined ...READ MORE

answered Nov 19 in Others by narikkadan
• 37,660 points
26 views
0 votes
1 answer

How to programmatically code an 'undo' function in Excel-Vba?

Add the command button to the worksheet ...READ MORE

answered Nov 25 in Others by narikkadan
• 37,660 points
26 views
0 votes
0 answers

How to open a new tab or a new window while writing an exam in AI - Onlime Remote Proctored

How to open a new tab or ...READ MORE

Jul 20, 2020 in Others by vimalkamal
• 120 points
2,485 views
0 votes
1 answer

How to create a navigation bar in Flutter app?

Hi@akhtar, To add a drawer to the app, ...READ MORE

answered Aug 25, 2020 in Others by MD
• 95,380 points
426 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
452 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,220 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
69 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
207 views
0 votes
1 answer

How to Freeze Top Row and Apply Filter in Excel Automation with C#

Try this: // Fix first row workSheet.Activate(); workSheet.Application.ActiveWindow.SplitRow = 1; workSheet.Application.ActiveWindow.FreezePanes ...READ MORE

answered Oct 22 in Others by narikkadan
• 37,660 points
109 views
0 votes
1 answer

How to hide and unhide the columns of an excel sheet using asp.net

Use this. For Row: worksheet_sub.Row(i).Height = 0; For Column: ...READ MORE

answered Oct 29 in Others by narikkadan
• 37,660 points
76 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP