Automatic Zoom to Width, while using Grouping.

[RickyDuvet]

Hi All,

I'm sitting behind a corporate firewall so posting pictures is not possible, so i'll counjour the image with words and copy/paste....maybe. Oh, I'm using Excel 2016.

I hold a data table, these headings, all in their own column. (This is a sample, my actual data table is 23 columns wide) With every new customer all colums are completed.
[TABLE="width: 883"]
<tbody>[TR]
[TD="class: xl65, width: 130"]Customer[/TD]
[TD="class: xl65, width: 103"]Replied?[/TD]
[TD="class: xl65, width: 145"]Date Received[/TD]
[TD="class: xl65, width: 84"]Status[/TD]
[TD="class: xl65, width: 113"]Notes[/TD]
[TD="class: xl65, width: 117"]Address[/TD]
[TD="class: xl65, width: 111"]Location Code[/TD]
[TD="class: xl65, width: 80"]Team ID[/TD]
[/TR]
</tbody>[/TABLE]


During the working day however, I usually only need the following to be visible, (Actually only 10 of my actual data table) so have hidden the others using grouping on the columns.

[TABLE="width: 438"]
<tbody>[TR]
[TD="class: xl65, width: 130"]Customer[/TD]
[TD="class: xl65, width: 117"]Address[/TD]
[TD="class: xl65, width: 111"]Location Code[/TD]
[TD="class: xl65, width: 80"]Team ID[/TD]
[/TR]
</tbody>[/TABLE]

What I woudl like to be able to do is to automate the "Zoom to Width" function so that when I expand or collapse, the number of columns visible always are er..."Zoomed To Width"

Thoughts/Suggestions?

Thanks in advance.

RickyD

 

[Yongle]

Here is a VBA method for you to expermient with

Place in sheet module (click sheet tab \ select View Code \ paste in code window \ {Alt}{F11} to go back to Excel )

Sub ColumnsAndZoom()
    Dim c As Variant
    Dim rng As Range:   Set rng = Me.Columns("A:W")
'hide all columns A:W
    rng.Hidden = True
'make specific columns visible
    For Each c In Array("A:B", "F", "S:W")
        Me.Columns(c).Hidden = False
    Next
'zoom to selection
    rng.Select
    ActiveWindow.Zoom = True
    rng.Resize(1, 1).Select
End Sub

Can be modified to provide user with choice of settings etc
- let me know if you would like to develop this idea futher

 

[Yongle]

Something else to consider ...

Have you played with Custom Views (see View tab)
- set up a few "views" with different columns visible
- Excel remembers which columns need hiding
- then add a simple bit of VBA to zoom and give user view options (via screen message)

 

[Yongle]

To give you an idea, with custom views named View1...View9 , the code to apply solution suggested in post#3 would look like this..

Sub ColumnsAndZoom2()
    Dim answer As String, msg As String, t As String, n As String
    t = vbTab: n = vbCr
    msg = 1 & t & "View1" & n & 2 & t & "View2" & n & 9 & t & "All Columns"
[COLOR=#ff0000][I]'ask user to choose, default is View9 (perhaps all columns visible)[/I][/COLOR]
    answer = InputBox(msg, "Choose view", 9)
[I][COLOR=#ff0000]'use that answer[/COLOR][/I]
    ActiveWorkbook.CustomViews("View" & answer).Show
[I][COLOR=#ff0000]'apply zoom to selection as before[/COLOR][/I]
    With Me.Columns("A:W")
        .Select
        ActiveWindow.Zoom = True
        .Resize(1, 1).Select
    End With
End Sub