Excel Userform Checkbox Initializeで設定したControlSourceを変更すると元々設定していたセルにおかしな値が書き込まれる

Question

Win10/Excel365環境(確か21H2)で、UserFormのCheckbox(Form Control)をInitializeでControlSoourceにあるセルを設定して表示後、

コマンドボタンでControlSourceを別のセルに変更すると、元々のControlSourceであったセルに入っていた値(今回の場合は数式：IF文でTrue/Falseを返している)を、

おかしな値で上書きしてしまう事象が発生しています。 ～ 数式結果がTrueなのに、"True"を書き込んだり"False"を書き込んだり、クリアしたりしてしまいます。

同じコマンドボタンで更に別のセルにControlSourceを変更した場合は上書きなどされず、元々入っていた数式のままになっています。

いろいろ試した中では、Initializeで設定したセルにだけ影響しているようです。

なお、同じForm上にあるTextBoxのControlSourceでは発生しておらず、CheckBoxだけで発生しています。

これの動作仕様や原因はどういうものかわかるでしょうか。　回避策をご教授いただけたらと思います。

よろしくお願いいたします。

※当サイトは自宅でしかアクセスできないかもしれませんので、レスポンスは遅くなる可能性があります

Answer 1

「UserFormのCheckbox」は Form Control ではありませんが、それはさておきこの「文章」だけではいかんともコメントしがたいかと。

Answer 2

ControlSoourceにあるセルを設定して表示後、

文章だけでは判断できませんでした。ブック固有の問題なのか、特定のバージョンに依存した障害なのか、コーディングの問題なのか…?

事象を明確にするため、現象を再現可能な小さなサンプルブックを作って OneDrive 等で公開していただきたいところなのですが…フォーラムに登録したばかりのようなので、投稿実績が足りておらず、もしかしたら URL は貼れないかな…。

UserFormのCheckbox(Form Control)をInitializeで

ややこしいことに、Form Control の Form とは UserForm のことでは無い という罠。

Form Control 版であれば、セルへのリンクは ControlSource ではなく ControlFormat.LinkedCell で行われるものですし、そもそも UserForm に貼ることもできません。

ControlSource を用いているということは、種別としては
シートに [開発]-[挿入]-[フォーム コントロール]で貼れる CheckBox (As Excel.CheckBox) ではなく
シートに [開発]-[挿入]-[ActiveX コントロール]で貼れる CheckBox (As MSForms.CheckBox2, As MSForms.CheckBox)の方に相当しますね。

①UserForm (As MSForms.UserForm2, As MSForms.UserForm)
　フォーム コントロール → ×使用できない
　ActiveX コントロール →  ○使用可能
　

②Excel ワークシート (As Excel.Worksheet; .Type = xlWorksheet)
　フォーム コントロール → ○使用可能
　ActiveX コントロール → ○使用可能
　

③Excel 4.0 マクロ シート(As Excel.Worksheet; .Type = xlExcel4MacroSheet)
　フォーム コントロール → ○使用可能
　ActiveX コントロール → ×使用できない
　

④Excel 4.0 インターナショナル マクロ シート(As Excel.Worksheet; .Type = xlExcel4IntlMacroSheet)
　フォーム コントロール → ○使用可能
　ActiveX コントロール → ×使用できない
　

⑤Excel 5.0 ダイアログ シート(As Excel.DialogSheet)
　フォーム コントロール → ○使用可能
　ActiveX コントロール → ×使用できない
　

Answer 3

再現できません。

(1) 新規ブックを作成し、Sheet1 を以下のように設定
　A1 →数式「=AND(B1>50,C1)」、セルの書式→保護タブ→ロック:ON
　B1 →固定値「100」、セルの書式→保護タブ→ロック:ON
　C1 →固定値「TRUE」、セルの書式→保護タブ→ロック:OFF

(2) Sheet1 を選択し、[ホーム]リボン - [セル] - [書式] - [シートの保護] をかける
　→ C1 セル以外は編集不可な状態になる
　→ この時点で A1 セルの数式は、値 TRUE を表示している状態

(3) UserForm1 を用意して CheckBox1 を貼り、以下のように設定
　ControlSource → 「Sheet1!A1」
　→ ControlSource を設定した瞬間、CheckBox1 の Value が「False」から「True」に変化する
　→ この時点で、Sheet1!A1 には元の数式「=AND(B1>50,C1)」がセットされたままの状態

(4) UserForm1 に以下を記述
Option Explicit
Private Sub UserForm_Initialize()
　　Me.CheckBox1.ControlSource = "Sheet1!C1"
End Sub　

(5) Sheet1!A1 をアクティブにした状態で UserForm1 を表示する
　→ A1 セルの内容は数式のままで、値 TRUE が表示されている
　→ CheckBox1 は ✔ が付いた状態で表示されている

(6) チェックボックスを1回だけクリック
　→ C1 セルの値が TRUE から FALSE に変化する
　→ A1 セルの内容は数式のままで、値が FALSE に変化する

(7) UserForm1 を右上×で閉じる

(8) UserForm1 のデザイン画面を開く
　→ ControlSource は、初期状態の「Sheet1!A1」のまま
　→ Value は、(3)時点で True だったが、現在は False になっている

(9) 再度 UserForm1 を表示する
　→ A1 セルの内容は数式のままで、値 FALSE が表示されている
　→ CheckBox1 は未チェック状態で表示されている
　→ CheckBox1 を操作すると、C1 セルが書き換わるだけで、A1 セルは数式のまま

　

なお、最初にシートを保護したのは、チェックボックスによって A1 セルが変更されるタイミングを調べるためです。

試しに UserForm_Initialize の処理をコメントアウトして A1 セルにリンクされたままにしてみます。

そうすると、初期状態で A1 セルの値がチェックボックスに反映されますが、チェックボックスを操作しようとすると直ちに阻害されます。(メッセージを閉じると、A1 セルの値は変化しないまま、チェックボックスの値だけ反転します)

もし、UserForm_Initialize 時点で A1 セルの値が数式から固定値に変更されていたのであれば、そのタイミングで上記のようなメッセージが発生していたはずです。

コメントアウトを解除して、UserForm_Initialize 時点で C1 セルを見るように書き換えたうえで、シートの保護も解除してみましたが、やはり A1 セルの値が固定値になる事象は確認できませんでした。

　

ただし、「保護されていない状態」で、「ControlSource が A1 を参照したまま」、「CheckBox1.Value の値を編集した場合」は別です。デザイン時なので UserForm_Initialize は実行されないまま、Sheet1!A1 の内容が数式ではなく、「FALSE」「」「TRUE」の 3 値いずれかに上書きされてしまうでしょう。

でもこれはデザイン時の話であって、「コマンドボタンでControlSourceを別のセルに変更する」タイミングの件とは無関係ですよね…。

Answer 4

返信が遅くなり申し訳ありません。…やはり会社では参照はできても返信ができませんでした。

UserForm上のCheckBoxコントロール等はActiveXでしたね。誤って理解していました。

魔界の仮面弁士さん、丁寧な説明をありがとうございました。

また、検証まで行っていただき、重ねて御礼申し上げます。　(スペルミスはごめんなさい)

検証いただいた動作で言うと、(4)のInitializeの後、”VBAコードでControlSourceを変更すると発生する”という事態です。

具体的には、

　・Sheet1のB2セルに、氏名などの文字列が入っている～実際にはA～E列の5つ、といった感じ

　・Cells(2,n)：ｎ=10～15　即ち J2:O2には、例：=If($A2="",True,False)といった式が入っている

という状態で、

Sub UserForm_Initialize()
  Dim n As Integer   'ループカウンタ
  BoxA = Worksheets("Sheet1").Cells(2,2).Value    'Form上のTextBox:BoxAにSheet1のB2セルの値をセット
  For n = 10 to 15
    MainMenu.Controles("CheckBox" & n).ControlSource = Sheets("Sheet1").Cells(2.n).Address
    '↑UserFormのFormObject名 ↑CheckBoxのObject名が"CheckBox10…"になっている
  Next n
  Sheets("Sheet1").Cells(2,1).Activate   'A2セルを選択
End Sub

Sub ChangeNext()
  Dim n As Integer  'ループカウンタ
  Dim R As Long  '現在選択されているセルの行番号を保持
  R = ActiveCell.Row
  BoxA = Sheets("Sheet1").Cells(R,2).Value
  For n = 10 To 15
    MainMenu.Controls("CheckBox" & n).ControlSource = Sheets("Sheet1").Cells(R,n).Address
  Next n
  Sheets("Sheet1").Cells(R,1).Activate  'A列R行のセルを選択
End Sub

という処理を行わせています。

Initializeで初期セットしたForm:MainMenu上の、TextBox/CheckBoxのControlSourceであるSheet1のセル座標を、

ChangeNextの処理で”初期値から'初めて'ControlSourceを変更する”と(セルの行を変えると)、

　　初期値としてセットしていたセルに”True”や”False”の「値」を書き込んだり、数式をクリアしてしまう

という事象が発生しているのです。

二度目のChangeNext処理の場合、つまり、一度目のChangeNext処理でControlSourceのセル座標を変更した後に、

再度ChangeNextを実行してみても、変更前のセルは何も影響を受けず、数式が保持されたままになっています。

また、Sheet1で離れた行のセルを選択し、コマンドボタン[選択]で、選択した行を基にしたControlSource変更処理も

行わせているのですが、初期値状態から当処理を実行すると、セルの数式結果がTrueなのにFalseを書き込んだりします。

これも、二度目の同操作/同処理を行うと、変な値が書き込まれることはなく数式が保持されたままになります。

これらから、

　　ControlSourceの初期値をコードで変更すると、初期値のセルに誤った値を書き込む

という動作が発生していると思わざるを得ないところなんです。

いかがでしょうか、何か原因や事例等の情報はないでしょうか。　回避策を探しております。

Answer 5

> ・Cells(2,n)：ｎ=10～15　即ち J2:O2には、例：=If($A2="",True,False)といった式が入っている

ということだと MainMenu を表示して CheckBoxN（N＝10～15）をクリックするとセルの数式は TRUE/FALSE に書き換わるので、そのあたりに起因してそうですが。提示されたコードは実際のものですか？（であれば普通はコピペするでしょうから Sub UserForm_Initialize の前には Private があって良さそうなものだし、タイプミスが混入することはないと思うので。）

1. MainMenu の表示はモードレスですか？　でないと UserForm_Initialize で Activate している A2 以外の行を選べませんが。
2. ChangeNext プロシージャはどこに書かれているのですか？（標準モジュールかフォームモジュールか）
   何がトリガとなって実行されるのですか？
3. コマンドボタン [選択] で実行されるプロシージャの内容は？（もしかしてこれが ChangeNext？）

Answer 6

皆さんいろいろとサポートをありがとうございました。

原因が見つかりました。

Sub ChangeNext()　等プロシージャーで、
 MainMenu.Controls("CheckBox" & n).ControlSource = Sheets("Sheet1").Cells(R,n).Address

としてあったはずが、

 MainMenu.Controls("CheckBox" & n).Value = Sheets("Sheet1").Cells(R,n).Value

と「Value」プロパティーを設定していて、ControleSourceを設定していなかったことが原因でした。

自宅でのテストでControleSource設定していて問題なく、会社環境で作成したものが誤動作していたので、

原因に気づかなかった次第でした。

会社環境でのテスト時に誰かが書き換えていた事実も判明したのですが、NETで見たコード例にValueを使うものがあり、

私のコードが間違っていると判断して書き換えていたとのことでした。 (もう！)

つまり、私のコードチェック不足が騒ぎの元だったという結果でした。　誠に申し訳なく。。。

ControlSourceプロパティーに修正し、正常動作するようになったことを確認しました。

今回、ほとんど触ったことのなかったExcelフォームで、コントロールのコツが掴めず苦しんでいますし、

複雑な要件が多く、操作の対象も多数のシートに行列入り乱れて参照する上に、フォームにも連携させる必要があり、

かつ、会社環境から/会社環境へファイルの出し入れや、印刷物の持ち出しなども禁止というルールのために、

記憶だけで自宅環境にテスト環境を構築する試練もあり、プログラマー職でもない者にはちょっとキツイところです。。

載せていたコードは、自宅テスト時のコードを一部コピペしているものの、公開できない部分も多く、

会社環境状態の記憶から、パターン化した内容を手打ちしてました。

…愚痴というか、言い訳ですみません。　m(_ _)m

ちなみに、Formはモードレス/Private宣言のプロシージャ/「次へ」等のコードはフォームモジュールに書いています。

トリガはフォーム上のコマンドボタンです。

おまけ的な話ですが、端末にサインインしているユーザーごとに、当該Excelファイルの利用時間制限機能も付けろと、

という要件も入っています。　NET調べて実現できてはいますが。

以上、みなさんのご指摘のお陰でミスを見付けることができました。　ありがとうございました。