VBAエキスパート対策
ユーザーフォームとメニューの操作

Excel VBAエキスパート対策です
最終更新日:2019-08-26

ユーザーフォームとメニューの操作


・ユーザーフォームの操作
・コントロールの操作
・メニューとツールバーの操作


2019/05/15からの「VBAエキスパート」リニューアルに伴い出題範囲から削除されました。
マクロVBA情報を中心に、エクセル関数・基本操作までサンプルとともに解説。初心者向けの基本から上級者向けの高度な内容までVBAサンプルコードを掲載解説しています。

試験としては、範囲が広すぎて問題が作りづらいところだと思います。
しかし実務としては、必ずユーザーフォームの必要性は出てきますので、一通りは学習しておくことをお勧めします。

【ここでのポイント】

ユーザーフォーム、コントロールについては、学ぶべきことが膨大にあります。
ユーザーフォーム・コントロールのそれぞれのプロパティ・イベントを知らなければVBAが書けないからです。
公式テキストでも、最も多くのページを割いています。

しかし、
実務では、VBEの画面にプロパィ一覧は表示されますし、入力候補も表示されますので、
例えば、スペルまでは覚えておく必要がありません。
ところが、MOS試験ではそう言う訳にはいかない為、ある程度は暗記しておく必要があります。

ただし、
試験に出されるプロパティ・イベントは限られているので、
公式テキストと本ページに書いてあるプロパティ・イベントを優先して覚えておけば良いでしょう。

ユーザーフォームの操作、コントロールの操作

以下で紹介している、ユーザーフォーム入門は、
画面サンプルを載せて自習できるようにしていますので、読みながら実際にやってみて下さい。

全部覚えようとしても大変なので、以下の点を意識しながら読んでください。

フォーム
UserForm.Show [vbModal | vbModeless]
Unload Me

Initializeイベント
QueryCloseイベント


コントロール
代表的なプロパティ


  Caption Value Text ListIndex ListCount List
コマンドボタン          
ラベル          
テキストボックス        
コンボボックス  
リストボックス  
チェックボックス        
オプションボタン        

ValueとTextは同じ値になります。
特段の使い分けはありません。

コンボボックスとリストボックスのListIndexは、
0から始まります。
未選択時は-1になります。

試験問題に出されたときに難しいのは、
コンボボックスとリストボックスになるでしょう。
この二つは、重点的に覚えて下さい。

代表的なメソッド
コンボボックスとリストボックスの
AddItemメソッド

代表的なイベント
Clickイベント
これは、全てのコントロールにありますが、
コマンドボタンでの使用がほとんどです。

実務では、
チェックボックスとオプションボタンで使いますが、試験には出る可能性は低いでしょう。

ユーザーフォーム入門
エクセル(Excel)マクロ(VBA)をやっていればユーザーフォームの存在を知り、そしてユーザーフォームを使いたくなります。使いたくなるというより、使った方が良い場合が出てきます。しかし、ユーザーフォームは少々とっつきにくく、使い始めて使いこなすには、各種コントロールの特性を理解し、イベントについても深い知識が必要になりす。
第1回.ユーザーフォームを挿入
ユーザーフォーム入門として基礎から解説します。まずは、ユーザーフォームの挿入です。ユーザーフォームの挿入手順 メニユーの「挿入」→「ユーザーフォーム」または、左ウインドウのプロジェクト内を右クリックし、「挿入」→「ユーザーフォーム」「UserForm1」が挿入されます。
第2回.フォームのプロパティ
ユーザーフォーム入門として基礎から解説します。フォームのプロパティの主要なものについて説明します。フュームの全プロパティ プロパティはたくさんありますね。これ全部理解するのは大変です。いずれは、全て理解するに越したことはないですが、とりあえず、以下に掲載している必要最低限のプロパティだけは把握しておきましょう。
第3回.コントロールの追加(コマンドボタン)
ユーザーフォーム入門として基礎から解説します。何もないフォームでは何もできませんので、とりあえず部品を配置しましょう。この部品をコントロールと呼びます。ツールボックス ツールボックスには、使えるコントロールが並んでいます。
第4回.コントロールの位置・サイズ調整
ユーザーフォーム入門として基礎から解説します。閉じるボタンだけでは何もできないので、もう少し追加します。複数のボタンを配置したら、位置やサイズを統一したいですね。OKボタンの追加と移動 とりあえず、OKボタンを追加しましょう。
第5回.ラベルの追加
ユーザーフォーム入門として基礎から解説します。フュームが表示されたとき、ユーザーが何をしたらよいか分からないと困ります。それには、日本語で分かりやすく表示しておく必要があります。自由に固定の文字を表示するにはラベルを使います。
第6回.テキストボックス(TextBox)の追加
ユーザーフォーム入門として基礎から解説します。フュームにユーザーが自由に入力できるテキストボックスを配置します。テキストボックスの追加 前回は、ラベルだけ追加しました。今回は、ラベルに対応するテキストボックスを追加しましょう。
第7回.テキストボックス(TextBox)の値をセルへ
ユーザーフォーム入門として基礎から解説します。ユーザーフュームのテキストボックスに入力した値を、ワークシートのセルに出力します。前回までに作成したユーザーフォーム 前回作成したユーザーフォームです。「OK」ボタンを押下すると、このテキストボックスに入力した値をワークシートのセルに値を設定するようにします。
第8回.セルの値をテキストボックスへ
ユーザーフォーム入門として基礎から解説します。フュームのテキストボックスにワークシートのセル値を表示します。シートの実行ボタンを押下したら、アクティブセルの行の値をフォームに表示してみましょう。Initializeイベントプロシージャーの追加 フォームのコードを表示しましょう。
第9回.標準モジュールとフォーム間のデータ受け渡しⅠ
ユーザーフォーム入門として基礎から解説します。フォームが起動された時、起動したシートのアクティブセルの行数を知る方法について解説します。前回までに作成したVBA 標準モジュール フォームモジュール PrivateSubUserForm_Initialize() DimiAsLong WithWorksheets("顧客マスタ") i=…
第10回.標準モジュールとフォーム間のデータ受け渡しⅡ
ユーザーフォーム入門として基礎から解説します。1.シートのセルを使う 2.標準モジュールのグローバル変数を使う 3.標準モジュールからフォームのコントロールを操作する 4.Subプロシージャーの引数を使う 5.Functionプロシージャーの戻り値を使う。
第11回.コンボボックス(ComboBox)の追加
ユーザーフォーム入門として基礎から解説します。ユーザーフォームで良く使われるコントロールは、ラベル、テキストボックス これは当然ですが、その次に良く使われるものは、コンボボックス、リストボックス、チェックボックス、オプションボタンになるでしょう。
第12回.リストボックス(ListBox)の追加
ユーザーフォーム入門として基礎から解説します。リストボックスについて解説します。リストボックスとコンボボックスの使い方はほぼ同様になります。前回コンボボックスで単一列(1列)の場合を説明しましたので、今回は特に複数列について詳しく説明します。
第13回.チェックボックス(CheckBox)の追加
ユーザーフォーム入門として基礎から解説します。チェックボックスは、ONかOFFかの2択の場合に使われます。チェックボックスの追加 今回は、顧客の休止状態のチェックボックスを追加してみましょう。オブジェクト名:chk休止 とすることにします。
第14回.オプションボタン(OptionButton)の追加
ユーザーフォーム入門として基礎から解説します。オプションボタンは、複数の項目からの単一選択になります。ラジオボタン、トグルボタンなどとも呼ばれるものです。前回のチェックボックスと混同されがちですが、チェックボクスはON/OFF、オプションボタンは複数からの選択になります。
第15回.ここまでの整理
ユーザーフォーム入門として基礎から解説します。一旦、ここまでの復習を兼ねて全VBAの整理をします。ついでに、シートの当該行のダブルクリックでフォームが開くようにします。ワークシート Worksheets("顧客マスタ") シートモジュール ダブルクリック時のイベントプロシージャーを追加しました。
第16回.アクティブコントロールに色を付ける
ユーザーフォーム入門として基礎から解説します。フォーム内のどこにカーソルがあるか分かりづらい場合があります。そこで、アクティブなコントロールのバックカラーを変えたり、対応するラベルのフォントを変更したりして、アクティブなコントロールを解り易くします。
第17回.Enterキーで次のコントロールに移動する
ユーザーフォーム入門として基礎から解説します。Enterキーの押下で、(タブ順が)次のコントロールに順々に移動してほしいものです。ユーザーフォームでのEnterキー動作について テキストボックスは、Enterキー押下で次のコントロールに進みます。
第18回.2段階コンボボックス
ユーザーフォーム入門として基礎から解説します。2段階コンボボックスを作成します。1つのコンボボックスの選択内容により、次のコンボボックスのリストを変更します。ワークシートのセルの入力規則で実施する場合は、入力規則のリストを、2段階の絞り込みで作成1 入力規則のリストを、2段階の絞り込みで作成2一覧リストを使用して、
第19回.テキストボックス数値編集
ユーザーフォーム入門として基礎から解説します。数値専用のテキストボックスの作成です。数字以外は入力出来ないテキストボックスを作成します。数値専用のテキストボックスの追加 以下のように金額のテキストボックスを追加します。
第20回.テキストボックスの各種イベント
ユーザーフォーム入門として基礎から解説します。ユーザーフォームで最も良く使われる、テキストボックスのイベントの一覧と、キー入力と、コントロール間の移動時のイベント発生順について記載します。ユーザーの操作に応じて自動処理するためには、その操作により発生するイベントと、その発生順序を理解しておくことが必要です。
第21回.ユーザーフォームの各種イベント
ユーザーフォーム入門として基礎から解説します。ユーザーフォームに対する操作(アクション)で発生するイベントの一覧です、ユーザーフォームそのものの制御をする場合は、このイベントを利用します。ユーザーフォームのイベント一覧 太字のイベントが良く使われるイベントになります。

上記のユーザーフォーム入門では、
最初の方は、初めてユーザーフォームを使う入門者を想定して、かなり詳しく説明しています。
後半になると、かなりの上級者向けの内容になっていますが、
完全に理解する必要はないので、最後まで読み切るようにして下さい。

メニューとツールバーの操作

この項目名についてはは、公式テキストにも書かれていますが2003以前の名称になっています。


以下では、
セルを右クリックした時のコンテキストメニュー(ショートカットメニュー)について記載します。

シートモジュールに以下を追加します。

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
  Dim cmdBra1 As CommandBarControl
  Dim cmdBra2 As CommandBarControl
  '標準状態にリセット
  CommandBars("Cell").Reset
  '全てのメニューを一旦削除
  For Each cmdBra1 In CommandBars("Cell").Controls
    cmdBra1.Visible = False
  Next
  '以下で、新規のメニューを追加
  Set cmdBra1 = CommandBars("Cell").Controls.Add()
  With cmdBra1
    .Caption = "AA"
    .FaceId = 2
    .OnAction = "AA"
  End With
  Set cmdBra1 = CommandBars("Cell").Controls.Add(Type:=msoControlPopup)
  With cmdBra1
    .Caption = "BB"
    .BeginGroup = True
  End With
  Set cmdBra2 = cmdBra1.Controls.Add()
  With cmdBra2
    .Caption = "BB1"
    .FaceId = 3
    .OnAction = "BB1"
  End With
  Set cmdBra2 = cmdBra1.Controls.Add()
  With cmdBra2
    .Caption = "BB2"
    .FaceId = 4
    .Tag = "説明"
    .OnAction = "'BB2(""" & .Tag & """)'"
  End With
  CommandBars("Cell").ShowPopup
  CommandBars("Cell").Reset
  Cancel = True
End Sub

覚えるべきプロパティ・メソッドを太字にしています。

.OnAction = "'BB2(""" & .Tag & """)'"
これは、OnActionで起動されるマクロに引数を渡しています。
ただし、記述が難しいので試験には出ません。


標準モジュールに以下を追加

Sub AA()
  ActiveCell.Value = "AA"
End Sub
Sub BB1()
  ActiveCell.Value = "BB1"
End Sub
Sub BB2(ByVal argTag)
  ActiveCell.Value = argTag
End Sub

OnActionで呼ぶプロシージャーは、必ず標準モジュールに作成します。


右クリックしてみましょう

シートモジュールを追加したシートでセルを右クリックすると

MOS VBA 画像

このように表示され、メニューをクリックすると、対応する文字がセルに入るようになります。


CommandBarオブジェクト

コマンド バーを表します。
CommandBarオブジェクトは、CommandBarsコレクションのメンバーです。

CommandBars("Cell")
CommandBarsコレクションの中から、"Cell"という名前のCommandBarオブジェクトを取得しています。
これが、右クリックで表示されるメニューになります。
"Cell"以外も沢山ありますが、試験には出ないはずです。
以下は、全てのCommandBarsの名前をイミィディエイトに出力しています。



Sub sample()
  Dim obj
  For Each obj In CommandBars
    Debug.Print obj.Name
  Next
End Sub

この右クリックメニューな中に、
Control(一つ一つのメニュー)の集まりとしてのControlsコレクションがあります。
以下は、全てのControlsの見出しをイミィディエイトに出力しています。

Sub sample()
  Dim obj
  For Each obj In CommandBars("Cell").Controls
    Debug.Print obj.Caption
  Next
End Sub

メニューを追加する時は、Controls.Addメソッドを使います。
メニューを削除する時は、Controls(インデックスまたは名前を指定).Deleteメソッドを使います。


コマンドバー.Controls.Add()

Addに指定する引数です。

名前 説明
Type 指定したコマンド バーに追加するコントロールの種類を指定します。
使用できる定数は、MsoControl クラスの
msoControlButton、msoControlEdit、msoControlDropdown、msoControlComboBox、msoControlPopup
のいずれかです。
Id 組み込みのコントロールを表す整数を指定します。
この引数を1に設定するか省略すると、指定した種類の空白のカスタム コントロールがコマンド バーに追加されます。
Parameter 組み込みのコントロールの場合、この引数はコンテナー アプリケーションでコマンドを実行するときに使用されます。
カスタム コントロールの場合、この引数を使用して、Visual Basic のプロシージャに情報を渡したり、Tag プロパティの 2 番目の値のようなコントロールの情報を格納することができます。
Before コマンド バーにおける新しいコントロールの位置を表す数字を指定します。
新しいコントロールは、指定した位置にあるコントロールの直前に挿入されます。
この引数を省略すると、コントロールは指定したコマンド バーの末尾に追加されます。
Temporary True を指定すると、新しいコントロールが一時的なものになります。
このコントロールは、コンテナー アプリケーションの終了と同時に自動的に削除されます。
この引数を省略すると、既定値の False になります。

上記サンプルでは、"BB"は親メニューになりますので、
Type:=msoControlPopup
これを指定しています。

省略している場合は、
Type:=msoControlButton
これが規定となります。


コントロールのプロパティについて

Caption
メニューに表示する文字列を設定します。

OnAction
起動するプロシージャ名を文字列で指定します。

FaceId
Type:=msoControlButtonの場合には、アイコンを指定できます。
この番号と実際のアイコンについては、「エクセルのアイコン取得」を参照して下さい。

State
公式テキストに書かれていますが、
このプロパティでチェツクを付ける方法が紹介されています。
実務で使う場面が想像出来ないのですが、一応おぼえておきましょう。

上記の使用例では、右クリックのイベント内なのでStateの切替は使えません。

Tag
任意の説明を設定できます。

【業務改善の実務】

ユーザーが入力しやすいように、ユーザーフォームを用意することは必用です。
ただし、
むやみにユーザーフォームを使えば良いという事も無く、
シートの形式を工夫すれば済む場合も多々ありますので、良く考えて設計するべきです。

凝ったユーザーフォームにイベントを大量に組み込んでいくと、
マクロがとても複雑になり、後々のメンテに苦労することになります。
VBAには、適切なコメントを書き込んでおくとか、
関連するイベントは、プロシージャ内の近くにまとめておく等の工夫は必ずしておくべきです。

右クリックは、
ボタンが配置出来ない時や、機能が多様になっている場合の選択方法としては有効な手段です。
VBAコードは定型的なものになるので、コピペで使い回せるようにしておきましょう。

【本サイト内の関連ページ】

Excelユーザーフォーム入門
エクセル(Excel)マクロ(VBA)をやっていればユーザーフォームの存在を知り、そしてユーザーフォームを使いたくなります。使いたくなるというより、使った方が良い場合が出てきます。しかし、ユーザーフォームは少々とっつきにくく、使い始めて使いこなすには、各種コントロールの特性を理解し、イベントについても深い知識が必要になりす。

右クリックメニューの変更(CommandBars)
セルを右クリックした時のショートカットメニューを変更します、右クリックメニューからマクロを起動できるようにすることで利便性が向上します・シートモジュールに以下を追加します。標準モジュールに以下を追加 SubAA() ActiveCell.Value="AA" EndSub SubBB1() ActiveCell.Value="…
エクセルのアイコン取得(FaceID)
エクセル内のアイコンを取得します。「右クリックメニューの変更」のFaceIdとして使用します。2003までなら、ツールバーが追加されます。2007以降では、リボンのアドインの中に追加されます。ConststartNoAsInteger=1'開始番号を指定 ConststopNoAsInteger=50'終了番号を指定 この数値を変更して実行して下さい。

VBAエキスパート公式テキスト

2019/5/30発売リニューアル版


2019/7/26発売リニューアル版

こちらは必須として購入した方が良いでしょう。
ちょっと高いなーとは思いますが、
書籍を購入することで、学習用データが提供されています。
・サンプルブック
・VBAエキスパート模擬問題
これらが使えるようになります。
このシリーズでは、テキストを読みながら学習していることを前提とします。



同じテーマ「MOS VBAエキスパート対策」の記事

プロシージャ
イベント
ステートメント(スタンダード)
関数
エラーへの対処
APIとOLEオートメーション
変数と配列
レジストリの操作
ファイルの操作
ユーザーフォームとメニューの操作
VBAスタンダード試験対策まとめ


新着記事NEW ・・・新着記事一覧を見る

ツイッターで出されたVBAのお題をやってみた|エクセル雑感(1月13日)
イベントプロシージャーの共通化(Enter,Exit)|ユーザーフォーム入門(1月13日)
Rangeオブジェクトの論理演算(差集合と排他的論理和)|VBA技術解説(1月10日)
イベントプロシージャーの共通化|ユーザーフォーム入門(1月7日)
コントロールの動的作成|ユーザーフォーム入門(1月6日)
Evaluateメソッド(文字列の数式を実行します)|VBA技術解説(1月5日)
エクスポート(PDF/XPS)|VBA入門(1月2日)
分析関数(OVER句,WINDOW句)|SQL入門(12月25日)
取得行数を限定するLIMIT句|SQL入門(12月21日)
外部ライブラリ(ActiveXオブジェクト)|VBA入門(12月21日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.マクロって何?VBAって何?|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ひらがな⇔カタカナの変換|エクセル基本操作




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ