列の表示／非表示をコントロール - Infomentのブログ～Excel VBA奮闘記～

こちらに寄せられたご意見に着想を得て、このようなものを作成してみました。

↓　コメントをいただいたブログ
infoment.hatenablog.com

↓　作ってみた

f:id:Infoment:20180711192242p:plain

Private Sub CommandButton1_Click() ' 閉じるボタン
    Unload Me
End Sub

Private Sub CommandButton2_Click() ' 全非表示ボタン

    Dim myFlag  As Boolean
    Dim i       As Long
    
    Select Case CommandButton2.Caption
        Case "全表示"
            myFlag = False
            CommandButton2.Caption = "全非表示"
        Case "全非表示"
            myFlag = True
            CommandButton2.Caption = "全表示"
    End Select
    
    Range("A:" & getColumnLetter(100)).EntireColumn.Hidden = myFlag
    For i = 0 To ListBox1.ListCount - 1
        ListBox1.Selected(i) = myFlag
    Next

End Sub

Private Sub ListBox1_Mouseup(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim i As Long
        Columns.Hidden = False
        For i = 1 To ListBox1.ListCount
            Columns(i).Hidden = ListBox1.Selected(i - 1)
        Next
End Sub

Private Sub UserForm_Initialize()
    Dim seq(1 To 100) As Variant
    Dim i As Long
    Dim j As Long
    
    j = 0    
    For i = 1 To 100
        ListBox1.AddItem StrConv(getColumnLetter(i) & "列（" & i & "列)", vbWide)
        If Columns(i).Hidden = True Then
            ListBox1.Selected(i - 1) = True
        Else
            j = j + 1
        End If
    Next
    
    If j = 100 Then
        CommandButton2.Caption = "全非表示"
    Else
        CommandButton2.Caption = "全表示"
    End If
End Sub

' akashi-keirin さんから紹介いただいた関数を使用
' http://akashi-keirin.hatenablog.com/entry/2017/08/27/171615

Public Function getColumnLetter(ByVal columnNumber As Long) As String
On Error GoTo errorHandler
  Dim Sh As Worksheet
  Set Sh = ActiveSheet
  Dim tmpStr As String
  tmpStr = Sh.Cells(1, columnNumber).Address
  Dim tmpArray As Variant
  tmpArray = Split(tmpStr, "$") 
  getColumnLetter = tmpArray(1)
  Exit Function
errorHandler:
  getColumnLetter = ""
End Function