【VBA】フォームの作り方

質問

サークルの活動管理表を作っています。

以下のようなエクセルの一覧に
「フォームでテキストを入力→ピボットテーブルで集計」
の流れです。
役職にはR,Tなどのアルファベットが5種類ほど入ります。

エクセル上ではこんな形でデータベースになっています。
ここに追加していくためのフォームです。
年 月 日 氏名ID　氏名　役職 区分ID　区分 金額

回答No.10

mesihuro · ベストアンサー

>この記述は前回教えてもらった　Sub　AAA()みたいに
>作って呼び出す形の方がいいでしょうか？
>何度か出てくるのです。

う～ん、自分なら関数にはしないかな。

あんまりメリットないしね。何十箇所もあるなら考えるけどね～

今のプログラムじゃせいぜい３～４箇所じゃない？

でも拡張性考えるなら悪いことではないと思うよ。

例えばこの先、「データが存在しない場合は違う場所からデータの取得を試みる」なんてことになるかもしれないからね。

自分ならそういうちょっと面倒な処理が追加されるときに関数化するかな。

まぁあくまで個人的見解だよ。
まとめられる箇所は全部関数にしないと気が済まない人だっているかもしれないしね～。

お礼

個人的な見解がほしかったので十分です。

ありがとうございます！

次段階のフォームに質問を写したいと思います！

回答No.9

mesihuro

>ComboBox2.ListIndex = 0
>対象セルの値が空白の時に
>「ListIndexプロパティを設定できません。プロパティ値が無効です」と出ます。
>回避できますか？
>イミディエイトウインドウで　-1となっているようです。

対象セルが空白の時に～って書いてあるから、何故このエラーが出るかは理解してるよね？

さて、ここで問題になっているのはセルが空白の時はどうするのかってところだ。

ループの終了条件は確か=""みたいな感じだったよね？

つまり最初が空っぽだった場合は1個も入らず終了してしまう。

でも後ろにはデータが残ってる。

ここからはちょっと仕様の問題もあるね。

ループ条件はそのまま(="")にするなら、ListIndexを設定するところで本当にリストが追加されているのかチェックする必要があるね。

そういう時はListCountっていうのを使うと簡単だよ。
ListCountは見たまんまだど、リストの要素数を返してくれるんだ。
If ComboBox1.ListCount > 0 Then
ComboBox1.ListIndex = 0
End If
こうしてあげるリストが1つ以上ある時だけIndexを設定してくれるようになるよ。

もし空白セルは無視して、後ろのデータだけは取りに行きたいなんて場合はループの終了条件を考え直す必要があるね。

お礼

こういう実用レベルの内容がわかる人ってすごいと思う。

補足

この記述は前回教えてもらった　Sub　AAA()みたいに
作って呼び出す形の方がいいでしょうか？
何度か出てくるのです。

回答No.8

mesihuro

>入っていればコンボボックスのEnabled = True 入ってなければEnabled = Falseだね

あ、ごめん。逆だねｗｗｗ

テキストボックスに値が入ってたらコンボボックスをグレーアウトだもんね。
Private Sub TextBox1_Change()
If TextBox1.Text = "" Then
ComboBox2.Enabled = True
Else
ComboBox2.Enabled = False
End If
End Sub

お礼

グレーアウトは使用しないことにいたしました。
ありがとうございます。

補足

ComboBox2.ListIndex = 0
対象セルの値が空白の時に
「ListIndexプロパティを設定できません。プロパティ値が無効です」と出ます。
回避できますか？
イミディエイトウインドウで　-1となっているようです。

回答No.7

mesihuro

>同じことを何度もするマクロAを
>抜き出してAを実行するみたいな記述があったような・・・
>その方法が正解かわかりませんが・・・。

マクロAを関数化すればいいのさ。う～ん、プログラムっぽくなってきたね～ｗ
↓みたいのを新しく書くのさ
Sub test()

End Sub

これだけでtestっていう関数ができあがる。でも今は何も処理書いてないから呼び出しても当然何もしないね～

()の中は引数を指定するんだけど今回はその説明は置いておこう。

このtestの中に共通で処理したい内容を書くんだ。

コンボボックスに追加する処理なら
Sub test()
ComboBox1.AddItem "A"
ComboBox1.AddItem "B"
ComboBox1.AddItem "C"
ComboBox1.ListIndex = 0
End Sub

そしてInitializeは
Private Sub UserForm_Initialize()
Call test
End Sub

とだけしてあげればいい。これでtestって関数はいつでも呼び出せるようになったのさ～。
今回はSubってので関数を作ったけど、Functionなんてのもあるよ。使い道が違うから調べてみるといいかもね～。

>現在、コンボボックスとテキストボックスを並列させており
>テキストボックスに値があればコンボボックスは無視する仕組みにしたいと思っています。
>・この場合コンボボックスとテキストボックスは両方必要であっていますか？
良いんじゃないかな？リストで固定かテキストで任意入力って感じで
まぁコンボボックス自体に値は入力可能だから、常にそれを取ってくるようにすればいいだけって気もするけどね。

もしそうするならグレーアウトとかのいらなくなるね～。

>・テキストボックスに入力したらコンボボックスはグレーアウト？させることは可能ですか？
>・その逆にテキストをクリアしたらコンボボックスのグレーアウトを解除したいです。
もちろん可能だよ。
グレーアウトはEnabledっていうのを指定するんだ。
ComboBox1.Enabled = True
ComboBox1.Enabled = False

Falseを指定するとグレーアウトになるよ、Trueだとグレーアウトが解除されるよ

後はテキストボックスのイベントを追加してIfで値が入ってるか確認してあげればいい。
入っていればコンボボックスのEnabled = True 入ってなければEnabled = Falseだね

でもこれはあくまでグレーアウトつまり選択できなくなってるだけで、値の取得は可能なんだ。

だから最後にやっぱりテキストボックスに値が入ってるかどうかのIfは必要になると思うよ。

ていうかコード書いても投稿するとインデント消えちゃうだね・・・

お礼

ココまで全てで来ました！グレーアウトは不採用にしました。

回答No.6

mesihuro

惜しい、本当に惜しい・・・

正解は

ComboBox1.Clear

Valueは余計なのさ～

お礼

出来ました！

補足

メインの機能ができつつあります！

つぎに金額のところになります。
プルダウンでの数値取り込みはできました。
ただ、フォームを開いた時と「区分」を変えたとき
同じマクロを走らせることになるのですが
2か所の記述が必要なのでしょうか？
同じことを何度もするマクロAを
抜き出してAを実行するみたいな記述があったような・・・
その方法が正解かわかりませんが・・・。

また金額のところは
現在、コンボボックスとテキストボックスを並列させており
テキストボックスに値があればコンボボックスは無視する仕組みにしたいと思っています。
・この場合コンボボックスとテキストボックスは両方必要であっていますか？
・テキストボックスに入力したらコンボボックスはグレーアウト？させることは可能ですか？
・その逆にテキストをクリアしたらコンボボックスのグレーアウトを解除したいです。

回答No.5

mesihuro

そしたら今どんな状態なのかまとめてみてね

エラーが出るのか、値は入るけど期待どおりの結果がでないのか。

そうしないとバグがあっても直せないんだ。

デバッグのワンポイントアドバイスなんだけど、ソースを書いてる画面の左端って灰色になってるちょっとしたスペースがあるよね？処理が書いてある行でそこの灰色の部分をクリックすると、茶色の●がつくんだ。これをブレークポイントっていうんだ。

これをつけると実行したあと、自動で処理がそこで止まるようになるんだ。
今回の場合であればIf Range・・・辺りの行につけて、処理がどうなってるのか見極めてみたらどうかな？

停止した後はF8で1行ずつ処理してくれるよ。

考え方自体は合ってると思うよ。もう一息だ、がんばれ！！

お礼

ありがとうございます。

補足

ComboBox1の値がドンドン追加されているので
毎回コンボボックス内をクリアしないといけない事に気づきましたが

ComboBox1.Value.Clear　で行けると思ったのですが、ダメでした。

回答No.4

mesihuro

お～、ループが使えるんだね。

>ちなみに、初期値としてJ2に入れて表示をさせておきたいです。

ちょっと良く分からないんだけども、コンボックスの一覧は追加できたんだよね？
その中からさらに特定のものを表示させておきたいって認識で回答するよ。

その場合はListIndexっていうのを設定するんだ。

書き方はこうだ

ComboBox1.ListIndex = 0

これの意味は、コンボボックスに追加されている"最初"の内容を表示するって意味なんだ。
補足のソースを見る限り、多分これだけでJ2の内容がデフォルトで表示されるようになると思う。

注意すべき点としては0が最初であること(つまり1が2番目)、そして現在一覧に追加されている要素数以上の値を指定するとエラーになっちゃうよ。

ちなみに0の部分は式を入れてもいいんだよ。
だから↓みたいな書き方しても結果は同じさ。
ComboBox1.ListIndex = 10-10

お礼

OK　ここまで動いています。

補足

'ComboBox1役職（C列）の条件で表示名を抽出

Private Sub ComboBox1_Change()

X = 2
name1 = "基本情報"

Do Until Worksheets(name1).Range("C" & X) = ""
If Worksheets(name1).Range("C" & X) = ComboBox1.Value Then
ComboBox3.AddItem Range("B" & X).Value 'Bの名前を表示
End If
X = X + 1

Loop
End Sub

これで役職ComboBox1の値を選ぶたびに
ComboBox3名前の部分の抽出をしてくれると思ったのですが
何かのタイミングで抽出はしてくれるのですが
ComboBox1を変えてもComboBox3の再抽出をしてくれないんですが
どうしたらいいんでしょうか？

回答No.3

mesihuro

やぁ、おはよう。
遅くまで頑張ってたみたいだね。

さてとりあえず補足について回答するよ。

>将来の変更のための布石なんですが
>コンボボックスの選択肢が変更した場合などを考えて
>エクセルシートにその枠を作っておき、そこから数値を取得する場合にはどうしたらいいですか？

特定のセルから取得したいってことでいいのかな？

セルから値を取得するには二通りのパターンがあるんだ。

一つが"Range"もう一つが"Cells"って関数なんだ。
書き方によって同じ動作ができるけど今回は分かりやすいRangeを使ってみるよ。
興味があればCellsも調べてみてね。セルの位置を移動させたいなんて場合は便利だよ。

さてRangeを使って特定のセルから値を取得したいなんてのはズバリこうだ

Range("A1").Value

これは現在選択されているシートのA1セルから値を取得するってことなんだ。
"**"は実在するセルなら書き換えて問題ないよ。

ここで注意してもらいたいのは"現在選択されているシート"ってことなんだ。
今回のような作業用のデータは別シートに書いておくのが一般的だと思う。
シートも一緒に指定したいときはこうだ

Worksheets("データシート").Range("A1").Value

これで特定のシートの特定のセルから値が取得できるはずだ。

つまりこれをそのままコンボボックスのAddItemの横に書いてあげれば完成ってことになる。
多分データはいっぱいだろうけど、今の知識で作ろうとすると同じような処理を何個も書くことになると思う。
ComboBox1.AddItem　Worksheets("データシート").Range("A1").Value
ComboBox1.AddItem　Worksheets("データシート").Range("A2").Value
ComboBox1.AddItem　Worksheets("データシート").Range("A3").Value

みたいにね。

これはもっとプログラムっぽく書く事はできるけど、それには条件分岐(if)、ループ(for~next・do~loop)みたいなことが必要になるから、とりあえず入門としてはズバズバ書けばいいと思うよ。

お礼

凄く丁寧で、余分な内容も役立ちます！

補足

J列10番目に設置
i = 2
Do Until Cells(i, 10) = ""
ComboBox1.AddItem Cells(i, 10).Value

i = i + 1
Loop
少し思い出して作ってみました。
これで取得ができました。

ちなみに、初期値としてJ2に入れて表示をさせておきたいです。
それはどうすればいいですか？

回答No.2

mesihuro

ふむふむ、フォームは作れたんだね

じゃぁここからはコーディングだね

上から順にやってみようか

まずデフォルトを設定したいならformをダブルクリックすると以下みたいのが出てくると思う
Private Sub UserForm_Click()

End Sub

これはフォームをクリックした時に発生するイベントなんだ。
今回は初期値を設定したいから、ダブルクリックして出てきた画面の右上にClickていうコンボボックスがあると思う。そこから"Initialize"ていうのを選ぶんだ。

これは画面が最初に表示されたときに自動て行ってくれる処理なんだ。

そうするとこんなのが出てくる
Private Sub UserForm_Initialize()

End Sub
これがでたら最初に出てるClickのヤツは消してかまわない。

そしたら今度は各コントロールに初期値を入れる処理を追加してあげる必要がある。
じゃぁまず
・コンボボックスにテキストを表示させる方法、デフォルトで指定する方法
・テキストボックスに今日の日付を自動的に取り込みさせる方法
この二つからやってみよう

コンボボックスにテキストを表示したいなら、"コントール名.Text"になるんだ
以下みたいな感じになるね。

Private Sub UserForm_Initialize()
ComboBox1.Text = "aaaa"
End Sub

ComboBox1ていうのはコンボボックスを選択すると、普通なら左にオブジェクト名が出てるはずだから、それと同じにしないといけないよ。もし存在しないものを使うと「そんなのねーよ、ばか」って怒られるよ

もしコンボボックスに値を追加しておきたいなんて場合以下みたいな感じだよ
Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "10"
.AddItem "20"
.AddItem "30"
End With
End Sub

次はテキストボックスに日付をデフォルトで入れておく作業だよ。
まず今日の日付を取ってきてくれる便利な関数がDateってやつなんだ
でもどうやらテキストボックスが3つあってそれぞれに年月日を入れたいみたいだね。
そうするとちょっとしか手間がかかるんだ。

これはあとで自分で調べてもらいたいんだけど、Formatっていう関数を使うんだ。
これは指定した書式変更してくれるものなんだ。
書いてみるとこんな感じだね。

Private Sub UserForm_Initialize()

TextBox1.Text = Format(Date, "yyyy")
TextBox2.Text = Format(Date, "mm")
TextBox3.Text = Format(Date, "dd")
End Sub

TextBox***てのはコンボボックスと同じように名前は一緒にするんだよ～。

お礼

凄くわかりやすい！

補足

将来の変更のための布石なんですが
コンボボックスの選択肢が変更した場合などを考えて
エクセルシートにその枠を作っておき、そこから数値を取得する場合にはどうしたらいいですか？

回答No.1

mesihuro

ソースは組めなくてもフォームはコントロールをポチポチ置くだけだよ。

どこが分からないのか記載してくれないと教えようがないよ

そもそもVBAのエディタすら開けてないのかな？

お礼

追記ありがとうございます

補足

あれから私もいじって整理するとわからないのが
・コンボボックスにテキストを表示させる方法、デフォルトで指定する方法
・テキストボックスに今日の日付を自動的に取り込みさせる方法
（上書きで任意日付にする必要あり）
・「役職」でフィルタした「氏名」をコンボボックスに出す方法
・金額は任意数値と選択式のため、たぶん図のようなコンボボックスだけではだめでテキストボックスも必要ではないか？（テキストボックス優先）
・「入力ボタン」を押したら、入力すべきテキストを確認して「OK」させる仕組み