Excel VBA テーブル操作TableStyleの設定変更

テーブル操作第3回目です。ListObjectオブジェクトのテーブルスタイルついて解説します。

くるみこ
くるみこ

前回のセル範囲をテーブルに変換する方法ではスタイルの設定は行っていませんでした。解除する方法でもスタイルを「無地」にできない場合がありましたね(^^;

そうですね、なんだかすっきりしない部分でした!
スタイルのことを勉強してスッキリさせたいと思います。よろしくお願いします!

【この記事でわかること
・テーブルスタイルとはどういうものかがわかります
・テーブルスタイルの見本シートを作成する方法

・テーブルスタイルの変更を見本シートから選択して適用する方法

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

くるみこ
くるみこ

前回記事は、セル範囲をテーブルに変換したり、テーブルを解除したりする方法を解説した記事です。覗いてみてね(^^)/

テーブルのスタイルを操作する

ListObject.TableStyle プロパティ

テーブルのスタイルを操作するには、テーブルスタイルを取得または設定する ListObject.TableStyle プロパティで行います。プロパティ値は「テーブルスタイルを表すオブジェクトを返します。」となっています。
TableStyleオブジェクトを取得したり設定できるということですね。

テーブルのスタイルを変更する

アクティブシートのテーブル(インデックス1)を対象とした場合のコードはこんな感じです。

・テーブルに設定してあるスタイル名を取得します。
 ActiveSheet.ListObjects(1).TableStyle.Name
・テーブルスタイルを変更するにはテーブルスタイル名を文字列で指定します。
 ActiveSheet.ListObjects(1).TableStyle = “TableStyleMidium2”
・テーブルスタイルを削除(無地に)するには空の文字列を渡しスタイル名を消します。
 ActiveSheet.ListObjects(1).TableStyle = “”
※ListObjects(1)の部分は、ListObjects(“テーブル名”)に置き換えることができます。

前回記事で気になっていた部分を変更してみました

気になっていたのは、テーブルを解除する際にスタイルを「無地」にする部分です
・変更したのは21行目に追加した、lobj.Range.ClearFormatsの1行だけです。
lobj.Range でListObjectの範囲全体をRangeで取得しています。
.ClearFormats でセル範囲の書式をクリアしています。この段階ではスタイルの書式は消えていません。

'【汎用】選択テーブルを範囲に変換する
Sub UnListObj()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lobj As ListObject
    'Application.InputBoxで列を指定できるようにする
    On Error Resume Next
    Set rng = Application.InputBox( _
        Prompt:="対象テーブル内のセルを選択してください", _
        Title:="対象テーブル選択", Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    rng.Worksheet.Activate
    'セルがListObjectかを確認してから処理する
    On Error Resume Next
    If rng.ListObject.Name = "" Then
        MsgBox "選択セルはテーブル(ListObject)ではありません。"
    Else
        Set ws = ActiveSheet
        Set lobj = ws.ListObjects(rng.ListObject.Name)
        lobj.Range.ClearFormats 'セル書式をクリアします
        lobj.TableStyle = ""    'スタイルを無地にする
        lobj.UnList             'テーブルを解除する
    End If
    On Error GoTo 0
End Sub

・書式を全て(テーブルとセル書式とも)クリアする場合のために覚えておきましょう。
・テーブルを解除する前に処理すれば簡単です。解除後に処理する場合は、セル範囲を改めて取得する処理をしなければなりません(^^;

テーブルスタイル名

Workbook.TableStyles はブック内で使用されているテーブルスタイル(TableStyle)オブジェクトが格納されているコレクションです。

・次のコードでスタイル名を確認してみましょう。

Sub GetTableStyleName()
    Dim tbStyl As TableStyle
    Dim i As Long
    For Each tbStyl In ThisWorkbook.TableStyles
        'Debug.Print tbStyl.Name
        i = i + 1
        Cells(i, 1) = tbStyl.Name 'A列1行目から全て書き出します
    Next
End Sub

・書き出されたのは次の170種類でした。内訳は次のとおりです。
  TableStyle 60種類(3種類の合計)
  PivotStyle 84種類(3種類の合計)
  SlicerStyle 14種類(3種類の合計)
  TimeSlicerStyle 12種類(2種類の合計)
・独自のスタイルを設定している場合などは数がもっと増えることになります。

・ここでは、TableStyle だけ内訳を確認します。(画像クリックで拡大できます)
TableStyleLight1~21

TableStyleMedium1~28

TableStyleDark1~11

スタイルを見本から選択して変更できるようにする

・テーブルスタイルを変更するには、スタイル名をテキストで指定する必要があります。
・手動では、見本から選択して設定できますのでVBAでも同じようにしたいと思います。

スタイル見本シートをVBAで作成します

・手動で作っても良いのですが、VBAで作ってみましょう。
テーブルスタイルは3分類で60種類あることがわかっています
・それを踏まえて必要なコードを考えます。手動では横並びに7個です。一つの見本は5行分です

'テーブルスタイルのサンプルシートを作成する
Sub MakeTblStyleSampleSheet()
    Dim r As Long, c As Long, i As Long
    Dim sName As String
    Sheets.Add Before:=Sheets(1)
    ActiveSheet.Name = "スタイル見本"
    '5行1列のTableStyleサンプルをヨコに7個タテに12個配置
    i = 1   'スタイル名Index初期値
    For r = 1 To 9  'スタイル配置タテのループ
        For c = 1 To 7  'スタイル配置ヨコのループ
            sName = ActiveWorkbook.TableStyles(i).Name
            'テーブル作成
            With ActiveSheet.ListObjects.Add( _
                SourceType:=xlSrcRange, _
                Source:=Cells(r * 6 - 5, c * 2 - 1).Resize(5, 1), _
                XlListObjectHasHeaders:=xlYes)
                '作成後のオブジェクトに設定
                .TableStyle = sName 'テーブルスタイル設定
                .HeaderRowRange.Value = sName '見出しにテーブルスタイル名代入
            End With
          i = i + 1
          If i > 60 Then Exit For  '60個作成できたら抜ける
        Next
    Next
    '列幅を調整(見本の間は1、見本はオート)
    With Columns("A:N")
      .ColumnWidth = 1  'まず全部1にしちゃう
      .AutoFit          'その後にオートフィットする
    End With
End Sub

・コードの説明は、コード内のコメントを参照してください。

テーブルスタイルを見本から選択して変更するコード

'テーブルスタイルを変更するサンプル
Sub TableStyleChangeSample()
    Dim tbStyl As TableStyle
    Dim i As Long
    Dim ws As Worksheet
    Dim rng As Range, tgrng As Range
    Dim lobj As ListObject
    'Application.InputBoxで列を指定できるようにする
    On Error Resume Next
    Set rng = Application.InputBox( _
        Prompt:="対象テーブル内のセルを選択してください", _
        Title:="対象テーブル選択", Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    rng.Worksheet.Activate
    'セルがListObjectかを確認してから処理する
    On Error Resume Next
    If rng.ListObject.Name = "" Then
        MsgBox "選択セルはテーブル(ListObject)ではありません。"
    Else
        Set ws = ActiveSheet
        Set lobj = ws.ListObjects(rng.ListObject.Name)
        'スタイル見本のセルを指定できるようにする
        Set tgrng = Application.InputBox( _
            Prompt:="現在のスタイルは" & lobj.TableStyle.Name & "です。" & _
                vbCrLf & "変更したいスタイルを見本から選択してください!", _
            Title:="対象スタイル選択", Type:=8)
        On Error GoTo 0
        If tgrng Is Nothing Then Exit Sub
        tgrng.Worksheet.Activate
        lobj.TableStyle = tgrng.ListObject.TableStyle.Name 'スタイルを変更する
        rng.Worksheet.Activate
    End If
    On Error GoTo 0
End Sub

・実行したGIF画像をご覧ください!(クリックで拡大表示できます)

・前回記事で作成したテーブルを解除するコードを流用しています。
・23行目からがスタイル見本を選択させる部分です。
・24行目で、Application.InputBoxを出して見本シートから適用するスタイルを選択させています。
・31行目で、選択した見本スタイルからスタイル名を取得して、スタイル変更しています。

スポンサーリンク

まとめ(おわりに)

・いかがでしたでしょうか?
「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
サンプルファイルを用意していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ
くるみこ

「テーブルスタイル」がどういうものなのかがわかったと思います。
スタイルの変更は、スタイル名を文字列で指定する必要があります。

すごくたくさんのスタイル名があるのでビックリしました(^^;
でも、スタイル見本から選んで適用できるなら便利ですね!

くるみこ
くるみこ

前回スッキリしなかったセル書式の部分も解決したので良かったです。次回は初回で位置情報の取得する例を紹介していますが、その部分をもう少し詳しく解説したいと思います(^^)

【今回分かったことは】
・テーブルスタイルとセル書式は別物ということがわかりました
・テーブルスタイルの種類と数がわかりました
・テーブルスタイルの見本シートを作成する方法
・サンプルから選択してテーブルスタイルを変更適用する方法


ブログランキングに参加しています(^^) 応援よろしくお願いしますm(_ _)m

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/

記事のサンプルファイルをダウンロードできます

今回の記事のサンプルをダウンロードできるようにしています
過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら(このリンク先)からご利用ください