エクセル表示列切り替えツール

FormExcelColumnSwitcher.frm.vba

Rem --------------------------------------------------------------------------------
Rem
Rem  @module        FormExcelColumnSwitcher
Rem
Rem  @description   エクセル表示列切り替えツール
Rem
Rem  @update        2021/09/19
Rem
Rem  @author        @KotorinChunChun (GitHub / Twitter)
Rem
Rem  @license       MIT (http://www.opensource.org/licenses/mit-license.php)
Rem
Rem --------------------------------------------------------------------------------
Rem  @references
Rem    Microsoft Forms 2.0 Object Library
Rem
Rem --------------------------------------------------------------------------------
Rem  @refModules
Rem    ClsBpca
Rem    ClsBpca - ClsBpcaCh
Rem
Rem --------------------------------------------------------------------------------
Rem  @history
Rem    2021/09/19 start 完成
Rem    2021/09/19 add   値の変更への追従に対応 ws_Change,GetHeaderRowRange
Rem
Rem --------------------------------------------------------------------------------
Rem  @note
Rem    アクティブシートのオートフィルタより上の情報を用いてワンタッチで
Rem    列の表示切り替えが行えるフォームを表示します。
Rem
Rem    アクティブブックのみに有効で、シート切り替えに追従します。
Rem
Rem    このプログラムはコントロール配列の作成のためaddinbox様のClsBpcaを使用しています。
Rem    http://addinbox.sakura.ne.jp/Bpca_Common.htm
Rem
Rem --------------------------------------------------------------------------------
Option Explicit

Private WithEvents wb As Excel.Workbook
Private WithEvents ws As Excel.Worksheet
Private WithEvents BtnShowAll As MSForms.CommandButton
Private WithEvents Btns As ClsBpca

Const BtnW = 200
Const BtnH = 30

Private Sub UserForm_Initialize()
    Set wb = Excel.ActiveWorkbook
    Set ws = Excel.ActiveSheet
    Set Btns = New ClsBpca
    Call CreateButtons
End Sub

Private Sub UserForm_Terminate()
    Btns.Clear
    Set Btns = Nothing
    Set wb = Nothing
    Set ws = Nothing
End Sub

Private Sub wb_SheetActivate(ByVal Sh As Object)
'    Debug.Print "SheetActivate : " & Sh.Name
    Set ws = Sh
    Call CreateButtons
End Sub

Private Sub CreateButtons()
    
    Rem 一旦ボタンをすべて削除
    Btns.Clear
    Set Btns = New ClsBpca
    Me.Controls.Clear
    
    Rem オートフィルタ直上の行数を求める
    Dim row表示切替数 As Long
    On Error Resume Next
        row表示切替数 = GetHeaderRowRange(ws).Rows.Count
    On Error GoTo 0
    
    Rem フォーム自体のデザイン
    Me.Caption = "エクセル表示列切り替えツール"
    Me.Width = BtnW
    Me.Height = BtnH * (row表示切替数 + 2)
    
    Rem すべて表示ボタンのデザイン
    Set BtnShowAll = Me.Controls.Add("Forms.CommandButton.1")
    With BtnShowAll
        .Width = BtnW
        .Height = BtnH
        .Top = 0
        .Caption = "すべて表示"
        .Font.Size = BtnH / 2
    End With
    
    Rem 選択肢ボタンのデザイン
    Dim btn As MSForms.Control
    Dim i As Long
    For i = 1 To row表示切替数
        Set btn = Me.Controls.Add("Forms.CommandButton.1")
        With btn
            .Width = BtnW
            .Height = BtnH
            .Top = (.Height) * i
            .Caption = ws.Cells(i, 1).Value
            .BackColor = ws.Cells(i, 1).Interior.Color
            .Font.Size = BtnH / 2
        End With
        Btns.Add btn
    Next
    Btns.Rgst BPCA_Except_MouseM, AllType:=True
End Sub

Rem すべて表示
Private Sub BtnShowAll_Click()
    ws.Columns.Hidden = False
End Sub

Rem 特定パターンの列を表示/非表示
Private Sub Btns_Click(ByVal Index As Integer)
'    Debug.Print "Btns_Click : " & Index & " : " & Btns(Index).Caption
    Dim rng As Excel.Range
    For Each rng In Excel.Intersect(ws.UsedRange, ws.Rows(Index))
        rng.Columns.Hidden = IsEmpty(rng.Value)
    Next
End Sub

Rem オートフィルタ外のセルの値が変化した時、ボタンを再構築する
Private Sub ws_Change(ByVal Target As Range)
    If Not Excel.Intersect(Target, GetHeaderRowRange(ws)) Is Nothing Then
        Call CreateButtons
    End If
End Sub

Rem ワークシートのテーブル・オートフィルタより上（ヘッダ領域）の行範囲を返す
Rem  ※AutoFilter.Rangeはワークシートイベント直後は取得に失敗する場合がありバグの原因となる場合があります。
Private Function GetHeaderRowRange(ws As Worksheet) As Range
    DoEvents
    On Error Resume Next
    
    Set GetHeaderRowRange = ws.Rows(1).Resize(ws.ListObjects(1).Range.Row - 1)
    If Not GetHeaderRowRange Is Nothing Then Exit Function
    
    Set GetHeaderRowRange = ws.Rows(1).Resize(ws.AutoFilter.Range.Row - 1)
End Function

横に長いテーブルを使うときに便利な、任意のシートの表示列切替ができるユーザーフォームです。

ユーザーは、テーブルやオートフィルタより上の行に、よく使用する表示列の組み合わせ（空欄以外の文字）を記述するだけで、このマクロを活用することができます。

使用風景動画ツイート
https://twitter.com/KotorinChunChun/status/1439279035792515073?s=20

※このフォームのコントロールはすべて動的配置です。フォームモジュールを作成し、空っぽのままコードを貼り付ければ動作します。

※AddinBox様のClsBpca.clsを使用しています。
開発環境の都合でClsBpcaを使っていますが、複雑なことはしていないので、ご自分でボタン共通化クラスを作れば必要ありません。