ＶＢＡでこんなものを作成したいのですが…

質問

こんばんは。回答者の方には
いつもお世話になっております..

Excelにコマンドボタンを付け、
コマンドボタンを押すと
チェックボタンの氏名が表示され
ひとつチェックをし、OKを押すと
Excelにある表の方に、
氏名と、ついでに性別を表示させる
そんなものを作成したいのですが…
ちなみに氏名と性別はテキストボックスへ
表示させたいのですが…;

私はCace文で作成できると思ったのですが
全く進まずです…;

これはマクロで可能なものなのか、
テキストボックスへの表示は可能なのか、
よろしければ参考になるサイトや
こんな方法があるなの、
どんなことでもいいので
教えていただきたいです…orz

宜しくお願い致します;

回答No.1

noname#144013 · ベストアンサー

こんにちは。

サンプルマクロを作ってみました。
宜しければ、試してみて下さい。
※目的のものと違っていた場合は、すみません。

■前提事項
１）ワークシートの名前は、"Sheet1"としています。

２）ワークシートに、以下の「コマンドボタン」を貼り付けているものとします。

　　　コマンドボタン１：　オブジェクト名　"CommandButton1"

　注）このコマンドボタンは、「コントロールツールボックス」より貼り付けた
　　　「コマンドボタン」とします。
　　　※「フォーム」ボックスの方の「ボタン」ではないものとします。

　このボタンのクリック時のイベントプロシージャ（ボタンクリック時に呼ばれる
　関数）を、ワークシートのコードモジュールに作成し、その中に、「ユーザー
　フォーム」を表示するマクロを記述します。
　※↑この処理は、サンプルマクロの、１）の下記部分で行っています。
　　　　　Private Sub CommandButton1_Click()

３）ワークシートに、以下の「テキストボックス」を貼り付けているものとします。

　　　テキストボックス１：　オブジェクト名　"TextBox1"
　　　　　　　　　　　　　　　　・このテキストボックスに「名前」を表示します。
　　　テキストボックス２：　オブジェクト名　"TextBox2"
　　　　　　　　　　　　　　　　・このテキストボックスに「性別」を表示します。

　注）これらのテキストボックスは、「コントロールツールボックス」より貼り付けた
　　　「テキストボックス」とします。

４）ワークシートの適当なセル範囲に、以下のような「名前」と「性別」を対応
　　づけたテーブル（名前テーブル）を作成しているものとします。

　　　＜名前テーブル＞
　　　　名前　　　　　性別
　　　　もぐら　　　　　 女
　　　　はむすたー　　男
　　　　かぴぱら　　　 男

　サンプルマクロでは、上記のテーブルが、E5:F8 のセル範囲に設定してある
　ものとしています。

　サンプルマクロでは、このテーブルを参照して、ユーザーフォーム上の
　「オプションボタン」の名称（キャプション）設定、及び、ワークシート上の
　「テキストボックス」への名前＆性別の表示を行うようにしています。

　この「名前テーブル」を、見せたくない場合は、セル範囲（列単位、または、
　行単位）を「非表示」に設定するか、または、テーブルを別シートに設定する
　ようにして下さい。
　※ただし、別シートに設定する場合は、マクロの変更が必要になります。

５）名前を選択する「ユーザーフォーム」を作成してあるものとします。

　　ユーザーフォーム：　オブジェクト名　"UserForm1"

　フォーム上に、以下のような部品（コントロール）を貼り付けてあるもの
　とします。

　　　オプションボタン１：　オブジェクト名　"OptionButton1"
　　　オプションボタン２：　オブジェクト名　"OptionButton2"
　　　オプションボタン３：　オブジェクト名　"OptionButton3"

　　　コマンドボタン１　 ：　オブジェクト名　"CommandButton1"
　　　　　　　　　　　　　　　　・[OK]ボタンとします。

　サンプルマクロでは、上記のオプションボタンの表示名（キャプション）を、
　ワークシート上の「名前テーブル」に設定してある名前を参照し表示する
　ようにしています。
　※↑この処理は、サンプルマクロの、２）の下記部分で行っています。
　　　　　Private Sub UserForm_Initialize()
　※↑これは、ユーザーフォームが起動（ロード）された際に呼ばれる、
　　初期化（イニシャル）のイベントプロシージャです。

　コマンドボタン１のクリック時のイベントプロシージャを、ユーザーフォーム
　のコードモジュールに作成し、その中に、ワークシートのテキストボックス
　への名前＆性別を表示するマクロを記述します。
　※↑この処理は、サンプルマクロの、２）の下記部分で行っています。
　　　　　Private Sub CommandButton1_Click()

■サンプルマクロ
注）インデント等のため、全角スペースを入れています。

１）ワークシートのコードモジュールに実装するマクロ
　（※標準モジュールに実装するマクロではありません）

/////↓ここから///////////////
'== [フォーム表示]ボタンのクリックイベント処理 ==
Private Sub CommandButton1_Click()
　　'「名前選択」フォームを表示
　　UserForm1.Show vbModal
End Sub
/////↑ここまで///////////////

２）ユーザーフォームのコードモジュールに実装するマクロ
　（※標準モジュールに実装するマクロではありません）

/////↓ここから///////////////
'== フォームの初期化 ==
Private Sub UserForm_Initialize()

　　Dim sh1 As Worksheet　'Worksheetオブジェクト取得用
　　Dim rgTbl As Range　　'Rangeオブジェクト取得用

　　'ワークシートの取得
　　Set sh1 = Worksheets("Sheet1")
　　'シート上の「名前テーブル」のセル範囲を取得
　　Set rgTbl = sh1.Range("E5:F8")

　　'ワークシートの「名前テーブル」の名前を
　　'オプションボタンのキャプションに反映
　　OptionButton1.Caption = rgTbl(2, 1).Text
　　OptionButton2.Caption = rgTbl(3, 1).Text
　　OptionButton3.Caption = rgTbl(4, 1).Text

　　'オプションボタンの状態を２値選択（True/False）のみに設定
　　'※Null値を許可しない
　　OptionButton1.TripleState = False
　　OptionButton2.TripleState = False
　　OptionButton3.TripleState = False

　　'オプションボタンの選択状態を初期設定
　　OptionButton1.Value = True
　　OptionButton2.Value = False
　　OptionButton3.Value = False

　　'オブジェクトの解放
　　Set rgTbl = Nothing
　　Set sh1 = Nothing

End Sub
'
'== [OK]ボタンのクリックイベント処理 ==
Private Sub CommandButton1_Click()

　　Dim i As Integer　　　'ループ変数
　　Dim sh1 As Worksheet　'Worksheetオブジェクト取得用
　　Dim rgTbl As Range　　'Rangeオブジェクト取得用

　　'ワークシートオブジェクト取得
　　Set sh1 = Worksheets("Sheet1")
　　'シート上の「名前テーブル」のセル範囲を取得
　　Set rgTbl = sh1.Range("E5:F8")

　　'オプションボタンの選択状態により、
　　'ワークシートのテキストボックスに名前と性別をセット
　　For i = 1 To 3　'オプションボタンの数だけループ
　　　　'オプションボタンがチェックONなら、対応する名前と性別を
　　　　'シート上のテキストボックスにセット
　　　　If Controls("OptionButton" & i).Value = True Then
　　　　　　sh1.OLEObjects("TextBox1").Object.Value = rgTbl(i + 1, 1).Text
　　　　　　sh1.OLEObjects("TextBox2").Object.Value = rgTbl(i + 1, 2).Text
　　　　　　Exit For　'ここでループを抜ける
　　　　End If
　　Next i

　　'オブジェクトの解放
　　Set rgTbl = Nothing
　　Set sh1 = Nothing

　　'フォームを閉じる
　　Unload Me

End Sub
/////↑ここまで///////////////

※添付画像は、上記マクロ実行時のExcelシート画面をキャプチャしたものです。

■補足事項
◎マクロの内容について
　上記マクロの記述で、解らない名称（メソッド、オブジェクト、プロパティなど）
　がある場合は、ExcelのVBE（Visual Basic Editor）のヘルプをご覧下さい。
　また、下記に上げた「参考サイト」もご覧になってみて下さい。

◎ユーザーフォームで選択する「名前」の項目追加について
　ユーザーフォームで選択する「名前」を追加する場合は、ワークシートの「名前
　テーブル」への項目追加および、ユーザーフォームのオプションボタンの追加
　を行えば可能です。（※その際は、マクロの変更も必要です。）

　しかし、項目数が多くなってくると、ユーザーフォームへのオプションボタンの
　追加作業が結構面倒ですし、１つのユーザーフォームの画面上に納めるのも
　限界があります。
　また、名前の数の変更がある度に、その都度、ユーザーフォームのデザイン
　変更＆マクロ変更を行うのが大変になります。

　このような場合、ユーザーフォーム上の名前選択は、オプションボタンではなく、
　リストボックスやコンボボックスを使用し、名前のリストを表示して、その中から
　名前を選択させるようにした方が良いと思います。
　そうすれば、名前の項目数が変わっても、ユーザーフォームのデザインは変更
　せずに済みます。
　また、マクロの変更も少なくて済みます。（あるいは、作り方によってはマクロも
　変更せずに済みます。）
　※もちろん、この場合でもワークシートの「名前テーブル」への項目追加は必要
　　です。

■参考サイト
以下に、Excelのマクロ（VBA）を作成する上で、参考になりそうなサイトを幾つか
上げておきます。

Excel(エクセル)VBA入門
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/index.html

Office TANAKA - Excel VBA Tips
http://officetanaka.net/excel/vba/tips/index.htm

Excel VBA を学ぶなら moug モーグ | 即効テクニック
http://www.moug.net/tech/exvba/

Excel VBA(エクセルマクロ)プログラミング講座＆Tips
http://www.happy2-island.com/excelsmile/

ワークシート上のコントロールを操作する
http://www.happy2-island.com/excelsmile/smile03/capter00716.shtml

以上です。参考になれば幸いです。

お礼

わわわー！( ゜Д゜)
とてもわかりやすくご説明頂き
本当にありがとうございます！
サンプルまで作成して頂き、
とても助かりました…っ!