【エクセル】オプションボタンにセルの値を反映させるには

質問

エクセルマクロ初心者です。教えてください。

シート２　の　あるセルの値が　A　のとき
シート１上の　オプションボタン１がtrueになる。
シート２　の　あるセルの値が　A以外のとき
シート１上の　オプションボタン１がfalseになる。

（オプションボタン１はフォームコントロールのオプションボタンです。）

という結果になるマクロの登録方法を模索していますがうまくいきません。
実際には、シート１は入力シートで、ボタンクリックで別の蓄積シートに転記（オプションボタンのほかコンボボックスなどの内容を転記します）。シート１上のリストボックスに蓄積シートの内容を表示し、選択するとその内容を呼び出すようにしたいのです。
コンボボックスやテキストボックスに呼び出すところまではなんとか本を見たりしてできたのですが、オプションボタンに呼び出せません。
なんとかお知恵をお借りしたいです。

よろしくおねがいします。

＊下記のようにやってみました。

Private Sub commandbutton3_click()

Dim n As Integer
n = ListBox1.ListIndex
If n = -1 Then
MsgBox "選択してください"
Else
ComboBox2.Value = ListBox1.List(n, 0)
ComboBox3.Value = ListBox1.List(n, 3)
ComboBox4.Value = ListBox1.List(n, 4)
ComboBox5.Value = ListBox1.List(n, 8)
ComboBox6.Value = ListBox1.List(n, 9)
ComboBox7.Value = ListBox1.List(n, 1)
ComboBox8.Value = ListBox1.List(n, 2)
TextBox3.Value = ListBox1.List(n, 10)
End if

＊＊＊＊＊＊＊＊＊＊＊ここからがうまくいきません＊＊＊＊＊＊＊＊

If Worksheets("入力シート").ListBox1.List(n, 6).Value = "A" Then
OptionButton１.Value = True

Else
OptionButton("A").Value = False

End If
End Sub

回答No.4

end-u · ベストアンサー

>A1がAならば、オプションボタンがOn
>そうでないならば　Off　　　　
>との設定のはずが、
>
>A1がAでも否でもOff
>という結果になります。
んん？
A1ってA1セルの事ですか？
もしそうなら、コードが全然違いますよ？
A1セルだったら
If .Range("A1").Value = "A" Then
'：
で良いんですが。
提示してない何処かに
.Range("A1").Value = .ListBox1.List(n, 6)
なんて書いてあったりするんですか？

If .ListBox1.List(n, 6) = "A" Then
　　.OptionButtons("Option Button 16").Value = xlOn
Else
'：
このコードの意味は、ListBox1で選択した行の7列目の値が　A　だったら　On　です。
（ListBoxの列を表す時、一番左が 0 からなので。）

ListBox1の、選択した値を確認してください。

>また、複数のoptionbuttonのon off を条件によって替えたいときは、
>ネットで調べたところ、if よりselect case の方がよさそうな記述をみましたがどう思われますか？
良いと思います。

Select Case .ListBox1.List(1, 6)
　　Case "A"
　　　　.OptionButtons("Option Button 16").Value = xlOn
　　Case "B"
　　　　.OptionButtons("Option Button 17").Value = xlOn
　　Case "C"
　　　　.OptionButtons("Option Button 18").Value = xlOn
End Select

個人の好みにもよりますが、可読性が良くていいんじゃないでしょうか。

お礼

いつもありがとうございます。

!!!!できました!!!!
なんでもお見通しなんですね。Listboxで選択した行の数え方が
間違っていました・・・。(n,6)でなく(n,5)でした。
こんな始末で全くお恥ずかしい限りです。

言い回しが悪くてすみませんでした。
「A1がAでも」というのは「あるセルがAでも」というつもりで
書いてしまい、「ListBox1(n,6)がAなら・・・」が正しいです。

select case 利用パターンもうまくいきました。
今晩はよく寝られそうです：）
最後まで丁寧に面倒をみていただき本当に感謝です。
ありがとうございました。
これからも地道にがんばります。

回答No.3

end-u

>もし、また気が向くようなことがあれば...
気が向いたので追記しますね :D

『言葉足らず』とは思っていません。
>シート１上の　オプションボタン１...
>シート１上のリストボックス...
と明記してありますし、提示コードを読み取れば凡その把握はできます。
Private Sub commandbutton3_click()
という記述から、Sheetに配置したCommandButtonのSheetモジュールに記載したClickイベントのコードだと推し測れます。
コード内容から、Sheetに配置しているのは[コントロールツールボックス]のコントロールだと推測されます。

ただし、ここで日本語の説明と食い違いがあります。
>＊以上すべてシート上に貼り付けたフォームです。
コントロール種別の把握が違うのではないでしょうか。

（先にも書きましたが）
　~~~~~~~~~~~~~~~~~~
ツールバー[コントロールツールボックス]のボタンなどはMSForms.Controlです。
ツールバー[フォーム]のボタンなどはExcel.Controlです。
私がおすすめしませんと書いたのは[コントロールツールボックス]のControlです。
貴方のコードを見る限り、
CommandButton と ListBox と ComboBox は [コントロールツールボックス]のControlです。
どちらのコントロールかはっきりしないと、コードの書き方、コントロールの指定の仕方が変わってくるので
動くものも動きません。

[コントロールツールボックス]のControlは、それを配置したシートモジュールのプロシージャの入力では、

Me.

とキー入力したあとに
Me.ComboBox1
　 ComboBox2
　 ComboBox3
などと入力候補が出てきます。
CommandButton1、ListBox1、ComboBox1、というように直接Object名で指定できます。
（Meはそのオブジェクトを示すキーワード）

対して、
>回答のようにやってみましたがエラーが出てしまいました。
>（実行時エラー”１００４”アプリケーション定義またはオブジェクト定義のエラー）
>私が使っているのは、コントロールでなくフォームだったので、xlOn xlOff の方で試しました。
OptionButtonが[フォーム]のOptionButtonでしたら、その名前を調べてください。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
オプションボタンを選択して、数式バー左の[名前Box]で調べてもいいです。

オプション x

と表示されているでしょう。（x は数値）

または、選択して
　　　　~~~~~~~~
Sub test()
　　MsgBox Selection.Name
End Sub
で調べてもいいです。

Option Button x

と表示されるでしょう。
[フォーム]のコントロールはその名前で指定できます。

Sub test2()
　　ActiveSheet.OptionButtons("Option Button 1").Value = xlOn
　　ActiveSheet.OptionButtons("オプション 1").Value = xlOff
End Sub
...という感じになります。

>（実行時エラー”１００４”アプリケーション定義またはオブジェクト定義のエラー）
実際にはどのモジュールの、どのコードで実行して、どの行でエラーで止まるのでしょう？
自力で解決できない場合は補足してください。

補足

毎日、時間を割いていただきありがとうございます。
夕べからチャレンジしていますが、うまくいかずにいます。

教えていただいたようにやった結果、エラーは出なくなり喜んでいましたがオプションボタンの動きが思うようにいきません。

A1がAならば、オプションボタンがOn
そうでないならば　Off　　　　
との設定のはずが、

A1がAでも否でもOff
という結果になります。

手動でOnにしておいて、実行（commandbutton3をクリック）するとOffになるので一応命令は聞こえているのだと思います。

>ツールバー[コントロールツールボックス]のボタンなどはMSForms.Controlです。
>ツールバー[フォーム]のボタンなどはExcel.Controlです。
>私がおすすめしませんと書いたのは[コントロールツールボックス]のControlです。
>貴方のコードを見る限り、
>CommandButton と ListBox と ComboBox は [コントロールツールボックス]の>Controlです。

なるほど。そうです　そのとおりです。
「コントロールツールボックス」のOptionbuttonと、「フォーム」のOptionButtonでは、書き方が違うということですね。
納得です。私は、commandbutton, listbox, comboBoxはコントロールツールボックスで作りましたが、OptionButtonは、「フォーム」で作りました（グループボックスで囲みたくて）。

「名前Box」で確認しました→「オプション１６」とありました
Sub test(　)もやってみました→「Option Button 16」と出ました。（エクセル2007でやったら出ました。）
ということで下記のように書きました。

Private Sub commandbutton3_click()
Dim n As Integer
With Me

n = .ListBox1.ListIndex

If n = -1 Then
MsgBox "選択してください"
Else
.ComboBox2.Value = .ListBox1.List(n, 0)
.ComboBox3.Value = .ListBox1.List(n, 3)
.ComboBox4.Value = .ListBox1.List(n, 4)
.ComboBox5.Value = .ListBox1.List(n, 8)
.ComboBox6.Value = .ListBox1.List(n, 9)
.ComboBox7.Value = .ListBox1.List(n, 1)
.ComboBox8.Value = .ListBox1.List(n, 2)
.TextBox3.Value = .ListBox1.List(n, 10)

If .ListBox1.List(n, 6) = "A" Then
.OptionButtons("Option Button 16").Value = xlOn
Else
.OptionButtons("Option Button 16").Value = xlOff
End If
End If
End With
End Sub
上記を何度もスペルを確認したり、少しかえてみたりとトライしてみたのですが・・・。何かアドバイスがあればお願いします。
（また気が向いたらでいいです：））
また、複数のoptionbuttonのon off を条件によって替えたいときは、
ネットで調べたところ、if よりselect case の方がよさそうな記述をみましたがどう思われますか？アドバイスがありましたらお願いしたいです。何度も回答いただき感謝です。なにとぞご負担にならぬよう
お願いします。
　Select case ????????(←ここはどのようになるのでしょうか)
Case .ListBox1.List(n, 6) = "A"
.OptionButtons("Option Button 16").Value = xlOn
Case .ListBox1.List(n, 6) = "B"
.OptionButtons("Option Button 17").Value = xlOn
Case .ListBox1.List(n, 6) = "C"
.OptionButtons("Option Button 18").Value = xlOn
End Select

回答No.2

end-u

そのコードがSheets("入力シート")のシートモジュールに書いてあるとして、
概ね
Dim n As Integer
With Me
　　n = .ListBox1.ListIndex
　　If n = -1 Then
　　　　MsgBox "選択してください"
　　Else
　　　　.ComboBox2.Value = .ListBox1.List(n, 0)
　　　　.ComboBox3.Value = .ListBox1.List(n, 3)
　　　　.ComboBox4.Value = .ListBox1.List(n, 4)
　　　　.ComboBox5.Value = .ListBox1.List(n, 8)
　　　　.ComboBox6.Value = .ListBox1.List(n, 9)
　　　　.ComboBox7.Value = .ListBox1.List(n, 1)
　　　　.ComboBox8.Value = .ListBox1.List(n, 2)
　　　　.TextBox3.Value = .ListBox1.List(n, 10)
　　　　If .ListBox1.List(n, 6) = "A" Then
　　　　　　.OptionButton1.Value = True
　　　　Else
　　　　　　.OptionButton1.Value = False
　　　　End If
　　End If
End With
こんな感じですが
（.ListBox1.List(n, 6).Value　とは書けません。Listプロパティをヘルプで確認してください）

>（オプションボタン１はフォームコントロールのオプションボタンです。）
[コントロールツールボックス]...MSForms.OptionButton の事で良いんですよね？　もし
[フォーム]...Excel.OptionButton の事でしたら

.OptionButtons("Option Button 1").Value = xlOn
.OptionButtons("Option Button 1").Value = xlOff
やIndex指定で
.OptionButtons(1).Value = xlOn
など。

#余談
#シート上にMSFormsのControlを多数配置すると結構不具合多く、あまりおすすめしません。

お礼

早急＆丁寧な回答をいただきありがとうございます。言葉足らずのところまでご理解いただき恐縮です。

回答のようにやってみましたがエラーが出てしまいました。
（実行時エラー”１００４”アプリケーション定義またはオブジェクト定義のエラー）
私が使っているのは、コントロールでなくフォームだったので、xlOn xlOff の方で試しました。

２番目の回答者のおっしゃるとおり、基本がないままやっているのと
シンプルに質問しようと思うあまりの説明不足の結果だと思います。
また、フォームのコントロールを多数配置すると不具合が多いとのアドバイスを受けて、ふりだしからやり直した方がいいか悩み中です。
もし、また気が向くようなことがあればアドバイスをください。

作りたいもの＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

sheet1（＝入力シート）

氏名（コンボボックス）出張日（コンボボックス）
用務名（コンボボックス）用務先（コンボボックス）
行き方　A電車（オプションボタン）・・・AとBグループボックスで囲む
　　　　B車　（オプションボタン）
入力決定（コマンドボタン）

呼出（コマンドボタン）
登録済みリスト（リストボックス）
表示（コマンドボタン）
修正（コマンドボタン）

＊以上すべてシート上に貼り付けたフォームです。
＊氏名、出張日、用務名、用務先、行き方を選び、「入力決定ボタン」　クリックでsheet2（＝蓄積シート）に転記＆蓄積されます。
＊氏名を選び、「呼出ボタン」クリックで、リストボックスにその人の　出張データが表示されます。（蓄積シートを氏名でオートフィルして　表示）
＊リストボックス上で選択したデータを「表示ボタン」クリックで、入　力シート上の出張日、用務名・・・のコンボボックス、オプションボタ　ンに表示させます。（←ここでつまづいてます）
＊表示させたデータを修正して再登録します。
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
実際にやりたいのはグループボックスで囲んだオプションボタンの操作なので質問の内容とは少し違うかもしれませんが、単純な一つのオプションボタンでもできなかったので、質問しました。
どうもありがとうございました。

回答No.1

imogasi

まずオプションボタンをシート上に貼り付けたのか、UserFormに貼り付けたのか認識がない（質問に書いてない。差が出ることを質問者は知らない）のは問題あり。
ーー
オプションボタンのON/OFFはセルの値と連動できる。
プロパティのLinkedCellをE1とかに設定。
E1のセルに式　=A1="A"　　と入れる。
（「あるセル」なんて、質問にあいまいな表現はしないこと。A1セルと仮定）
これでA1がÅならオプションボタンがON、それ以外ならOFFになって連動する。VBAコードだけでやりたいのだろうが、エクセルの本筋の方を勉強してからにしてほしいのであえて書く。
リストボックス、コンボボックスともLinkedCellがある。
ーーー
ListBoxとComboboxを使っているところは何がしたいのか。その仕組みは判りにくい。コードだけ書かないで、説明したら。
あるいは質問に関係ないのかも知れない。
エクセルらしい、エクセルの入力規則（VBA利用）では出来ないのか。

お礼

回答をありがとうございます。
お見込の通りでユーザーフォームは使ったことがありません。
ツールバーのフォームの進化したものがコントロールだということぐらいしかわからずにやっております。
E1の例：セルに＝を２つも使うやり方は初めて知りました。
勉強になりました。LinkedCellはリンクされたセルに表示させるだけかとおもっていましたので目から鱗でした。
ありがとうございました。