先日書いた記事に下記コメントをいただきました。

複数の「グループ」の表示/非表示をリボンからではなく、アドインのユーザフォームのコントロール（チェックボックス等）から行う方法をご教示下さい。これによりExcelクローズ時の状態をファイル等に保存しておいて次回のExcel起動時のデフォルトにしたいと思っています。

シートに設定値を保存するのは現在のアプリに実装ずみなのでOKです。
問題はユーザフォーム上のチェックボックスによってトグルで特定のグループの表示/非表示を動的に切り替えたいです。そのときのInvalidateControl周りの記述方法が分かりません。

UIのonLoadでPublic変数のリボンをセットしたあとに、チェックボックスの記述はどうなるのでしょうか？お手数ですがご教示頂けますと幸いです

グループの表示・非表示をユーザーフォーム上のチェックボックスで切り替え、その表示状態をどこかに記録しておきたい、というものです。

この場合、使用するのはもちろん、group要素のgetVisible属性です。

問題なのは、表示・非表示を設定するコールバック関数の第二引数をどうやって設定するかですが、今回はシートに設定値を保存するということなので、下記のように設定保存用のシートから読み取ることにします。

下準備

設定保存用のシート(シート名：設定)を作成し、下図のようにセルB1～B3(3つのグループの表示状態を制御するとして)にグループの表示状態の初期値を入力します。

リボンXML

<?xml version="1.0" encoding="utf-8"?>
<customUI onLoad="rbnSample_onLoad" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab id="tabSample" label="Sample Tab">
        <group id="grpSample1" label="Group 1" getVisible="grpSample_getVisible">
          <button id="btnDummy1" label="Button 1" imageMso="HappyFace" size="large" />
          <button id="btnDummy2" label="Button 2" imageMso="HappyFace" size="large" />
          <button id="btnDummy3" label="Button 3" imageMso="HappyFace" size="large" />
          <button id="btnDummy4" label="Button 4" imageMso="HappyFace" size="large" />
        </group>
        <group id="grpSample2" label="Group 2" getVisible="grpSample_getVisible">
          <button id="btnDummy5" label="Button 1" imageMso="SadFace" size="large" />
          <button id="btnDummy6" label="Button 2" imageMso="SadFace" size="large" />
          <button id="btnDummy7" label="Button 3" imageMso="SadFace" size="large" />
          <button id="btnDummy8" label="Button 4" imageMso="SadFace" size="large" />
        </group>
        <group id="grpSample3" label="Group 3" getVisible="grpSample_getVisible">
          <button id="btnDummy9" label="Button 1" imageMso="HappyFace" size="large" />
          <button id="btnDummy10" label="Button 2" imageMso="HappyFace" size="large" />
          <button id="btnDummy11" label="Button 3" imageMso="HappyFace" size="large" />
          <button id="btnDummy12" label="Button 4" imageMso="HappyFace" size="large" />
        </group>
        <group id="grpSample4" label="Group 4">
          <button id="btnSample" label="フォーム表示" imageMso="HappyFace" size="large" onAction="btnSample_onAction" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

標準モジュール

'標準モジュール
Option Explicit

Public myRibbon As Office.IRibbonUI

Public Sub rbnSample_onLoad(ribbon As IRibbonUI)
  Set myRibbon = ribbon
End Sub

Public Sub grpSample_getVisible(control As IRibbonControl, ByRef returnedVal)
  Dim sh As Excel.Worksheet
  
  Set sh = ThisWorkbook.Worksheets("設定")
  Select Case control.ID
    Case "grpSample1": returnedVal = sh.Range("B1").Value
    Case "grpSample2": returnedVal = sh.Range("B2").Value
    Case "grpSample3": returnedVal = sh.Range("B3").Value
  End Select
End Sub

Public Sub btnSample_onAction(control As IRibbonControl)
  UserForm1.Show False
End Sub

ユーザーフォーム(UserForm1)

'UserForm
Option Explicit

Private configSheet As Excel.Worksheet

Private Sub CheckBox1_Change()
  configSheet.Range("B1").Value = Me.CheckBox1.Value
  myRibbon.InvalidateControl "grpSample1"
End Sub

Private Sub CheckBox2_Change()
  configSheet.Range("B2").Value = Me.CheckBox2.Value
  myRibbon.InvalidateControl "grpSample2"
End Sub

Private Sub CheckBox3_Change()
  configSheet.Range("B3").Value = Me.CheckBox3.Value
  myRibbon.InvalidateControl "grpSample3"
End Sub

Private Sub UserForm_Initialize()
  Set configSheet = ThisWorkbook.Worksheets("設定")
  Me.CheckBox1.Value = configSheet.Range("B1").Value
  Me.CheckBox2.Value = configSheet.Range("B2").Value
  Me.CheckBox3.Value = configSheet.Range("B3").Value
End Sub

ポイントは下記4点です。

1. IRibbonUIオブジェクトの変数(myRibbon)をPublicにする。
2. getVisible属性のコールバックを一つにまとめる場合は、第一引数であるIRibbonControlオブジェクトのIDプロパティで処理を分ける。
3. group要素の表示状態を、設定用のシートから読み取るようにする。
4. チェックボックスのイベントで、チェックボックスの値を設定用シートの値と連動させ、InvalidateControlメソッドによって表示・非表示を切り替える。

今回は簡単なサンプルなので処理が冗長なままですが、下記thom氏のブログ記事のような方法を使えば、よりシンプルに設定用シートから値を読み出せるようになります。

t-hom’s diary

VBA マクロの設定をシートに保存する場合のテクニック - t-hom’s diary

https://thom.hateblo.jp/entry/2015/06/21/021207

Excelマクロの設定をシートに保存しておくことがよくある。ただメインマクロから直接Range指定で設定を取り出すと、シートの構成が変わった場合にマクロを編集しなければならないので面倒である。 そこで、シートの設定はシート自身のプロパティとして持たせておくと、変更箇所が局所的になって後々メンテナンスしやすい。文章だけでは意味が分からないと思うのでコードを書いて解説する。 シートの準備 まず、Book1.xlsmに設定シートを次のように作る。このとき、VBEのプロパティウインドウで、設定シートのオブジェクト名も「設定」...

t-hom’s diary

VBA マクロで利用する設定値をシートに保存し、文字列でアクセスするテクニック - ...

https://thom.hateblo.jp/entry/2018/06/16/035609

今回はマクロで利用する設定値をシートに保存し、汎用的に文字列でアクセスできるようにするテクニックの紹介。 作り方 標準モジュールを挿入し、プロパティウィンドウからオブジェクト名をConfgに変更する。 Configモジュールに以下のコードを張り付ける。 Option Explicit Private configurations As Variant '<- configulations is keepd here as variant array. Private Property Get InitialSettings() As Collection Set InitialSettings…

また、制御したい要素が多い場合には、クラスを利用して処理をまとめた方がコードもすっきりするでしょう。

t-hom’s diary

VBA フォームのコントロールイベントを共通化する - t-hom’s diary

https://thom.hateblo.jp/entry/2016/03/11/204324

こちらの記事を読んだところ、同じようなChangeイベントプロシージャがコントロールの数だけできてしまうことにお悩みの様子。 kantoku.hatenablog.com面白そうなので色々調べながらやってみた。参考にしたページはこちら 3.4.3 共通イベント処理クラス - EXCEL-VBA開発講座ateitexe.com まずEventControlというクラスモジュールを用意した。 コードはこちら Public WithEvents chk As MSForms.CheckBox Public WithEvents txt As MSForms.TextBox Public Sub セッ…

Ateitexe アテークゼ

ExcelVBAでControlオブジェクトのChangeイベントを一括制御する方法

https://ateitexe.com/excelvba-control-change-event/

たくさんあるTextBoxなどのChangeイベントを、ひとまとめにして制御する方法を解説しています。