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, 2022 in Others by Kithuzzz
• 38,020 points
786 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, 2022 by narikkadan
• 63,720 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, 2022 in Others by narikkadan
• 63,720 points
917 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, 2022 in Others by narikkadan
• 63,720 points
999 views
0 votes
1 answer

How to divide data in excel into 4 columns whose sum is almost equal to 1/4 of the sum of all values/

5049 is the sum of all numbers, ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,720 points
689 views
0 votes
1 answer

How to hide blank rows in an excel form if they are blank

If an empty row should be defined ...READ MORE

answered Feb 13, 2023 in Others by narikkadan
• 63,720 points
713 views
0 votes
1 answer

Having trouble hiding activecell column across tabs using vba

You might keep track of the location ...READ MORE

answered Feb 9, 2023 in Others by narikkadan
• 63,720 points
566 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,740 points
1,107 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
3,538 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, 2022 in Others by gaurav
• 23,260 points
828 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, 2022 in Others by narikkadan
• 63,720 points
2,578 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, 2022 in Others by narikkadan
• 63,720 points
1,611 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