VBA ユーザーフォーム

質問

VBAにおけるユーザーフォームの件

今，下記の様なプログラムを組んでいるのですが，「myComboBox」に入った？値をこの後で使用したいのですが，

どうすればいいのかわからなくて困っています．

これで何がしたいかというと，ある個数分のコンボボックスを自動で作成して使用しようとしているのです．

Private Sub UserForm_Initialize()
Dim a As String
Dim jj As Long
Dim s As Integer
Dim myComboBox As Control

N = InputBox("抜き出したいデータ数は？")
EffectiveRow = Range("A65536").End(xlUp).Row
Effectivecolumn = Cells(2, 16384).End(xlToLeft).Column

For s = 1 To N
Set myComboBox = Me.Controls.Add("Forms.ComboBox.1")
With myComboBox
.Height = 20
.Width = 150
.Left = 120
.Top = (s - 1) * .Height + 10
End With
For jj = 1 To Effectivecolumn
myComboBox.AddItem Worksheets(1).Cells(1, jj).Value
Next jj
a = myComboBox.Value
Worksheets(2).Cells(1, 1) = a
Next s

End Sub

回答No.1

end-u · ベストアンサー

まず、
>For jj = 1 To Effectivecolumn
>myComboBox.AddItem Worksheets(1).Cells(1, jj).Value
>Next jj
ここはLoopしなくてもColumnプロパティを使って
myComboBox.Column = Worksheets(1).Cells(1).Resize(, Effectivecolumn).Value
..とセットできます。
ただ、
Worksheets(1).Cells(1).Resize..
これだと複数のComboBoxの場合でも同じデータがセットされてしまいます。
そういう仕様なのでしょうか？
１行ずつズラすなら
Worksheets(1).Cells(s, 1)..
などとする必要があるのでは。

>「myComboBox」に入った？値をこの後で使用したいのですが，
この後とはどんなタイミングなのでしょう？
例えば
Private Sub CommandButton1_Click()
　　MsgBox Me.Controls("combobox1").Value
End Sub
こういう使い方でしょうか。

セットした値の全てを配列に取得したいならListプロパティを使って
Dim ary
ary = Me.Controls("combobox1").List
MsgBox ary(3, 0)
..ともできます。
が、その場合は元のセルを参照すれば済みますから
前者のMe.Controls("combobox1").Value..のほうですか。

お礼

end-uさん

早速の回答ありがとうございます．

早速なのですが，
For jj = 1 To Effectivecolumn
myComboBox.AddItem Worksheets(1).Cells(1, jj).Value
Next jj

これは，1行目の文字列を集めてコンボボックスで表示・選択するようにしています．

簡単に書くのであれば，
dim a as string

a=combobox1.value

if a=・・・

という風にコンボボックスで選択した値を使用できますが，今回はset関数を使用しコンボボックスを大量に自動で作成して，このような操作を行いたいのですが，それぞれのコンボボックスを定義して使用する方法がわからないのです．

わかりににくいかもしれませんが，よろしくお願いいたします．

回答No.3

end-u

re:#1の内容は質問に書かれている事と変わりなく、それについては回答してるつもりです。
動的にComboBoxを追加してるので
Me.ComboBox1.Value..と書けないって事ですよね。
だから
Me.Controls("combobox1").Value..のように名前で指定してあげれば良いです。

>この後とはどんなタイミングなのでしょう？
のお応えがないので推測８割ですが

Option Explicit

Private N As Long

Private Sub CommandButton1_Click()
　　Dim i As Long
　　For i = 1 To N
　　　　Worksheets(2).Cells(i, 1).Value = Me.Controls("combobox" & i).Value
　　Next
End Sub

Private Sub UserForm_Initialize()
　　Dim i As Long
　　Dim Effectivecolumn As Long

　　N = Application.InputBox("抜き出したいデータ数は？", Type:=1)
　　If (N < 1) Or (N > 10) Then Exit Sub
　　Effectivecolumn = Worksheets(1).Cells(2, 16384).End(xlToLeft).Column
　　If Effectivecolumn = 1 Then Effectivecolumn = 2
　　For i = 1 To N
　　　　With Me.Controls.Add("Forms.ComboBox.1")
　　　　　　.Height = 20
　　　　　　.Width = 150
　　　　　　.Left = 120
　　　　　　.Top = .Height * i - 10
　　　　　　.Column = Worksheets(1).Cells(i, 1).Resize(, Effectivecolumn).Value
　　　　End With
　　Next
End Sub
こんな感じなのでしょうか。

提示コードはUserForm_Initialize..ユーザーフォーム初期化イベントの中で
>a = myComboBox.Value
>Worksheets(2).Cells(1, 1) = a
とされてますが、ユーザー操作の前なのでComboBox.Valueには何もはいっていません。
ユーザー操作後、例えばCommandButton1_Clickのタイミングで
Me.Controls("combobox" & i).Value..などのように名前指定で取り出せば良いです。

動的に追加されるから
Me.Controls("combobox1")
Me.Controls("combobox2")
Me.Controls("combobox3")
：
のような名前になるでしょう。

または、仕様の話になりますが、
ComboBoxを際限なく作るわけではないでしょうから、
上限個数のComboBoxを作りVisibleをFalseにしておいて、
使用時にN個のみVisible = Trueで対処する..
..という事も考えられなくはないです。
For i = 1 To N
　　With Me.Controls("combobox" & i)
　　　　.Visible = True
　　　　.Column = Worksheets(1).Cells(i, 1).Resize(, Effectivecolumn).Value
　　End With
Next

回答No.2

imogasi

まず読者回答者のために、簡単にして、シートのデータ実例を挙げて質問すること。
実際は多数列（行）あるのかもしれないが、少数に簡略化して掲出質問すること。
また何がしたいのか、文章で説明すること。（初心者は考え方
取り掛かり方そのものが適当でない・珍奇なことも多い）
コードだけコピペして、読み解けというのは、読者のことを考えてない。
この文書での説明能力が無いということは、疑問点が明確になってない証拠だ。
質問者は、ＷＥＢかなんかのプログラムによる、多数のコントロール作成のコードを
見つけてきて、少し、いじくっただけではないのか。
普通エクセルＶＢＡの大部７００ページぐらいの解説書でも、プログラムによる
コントロールの作成例は載っていないだろう。エクセルでは、そこまで皆が
使うとしてないのだと思う。１０－２０ぐらいを、手動操作でフォーム上に作る
前提だと思う。
初心者には過ぎたやり方だと思う。
本質問では何列ぐらいのデータを扱うのか？
ーー
そもそも、コンボボックスとはどういうことのためにあるのか？
人間・使用者がコンボの項目のうち、適当な項目を選択して、その選択内容でその後
処理したり、エクセルではセルの値にセットするものでしよう。
人間の選択判断が必要だから、この仕組みを使うはず。その点の認識が薄いのでは。
ーー
コンボを利用するには
１．フォームにコンボを作成
２．実行（結果として、ユーザーフォームが表示される）
３．人間がコンボの項目を選択
４．その後本当にやりたい処理（メイン処理を）する
これらは別々の作業単位に作成され１，２の時間と、３，４の時間は
切り離している。
それをつなぐのがコントロールのイベントでしょう。
質問者のコードではこれ（イベント）が現れてないのでは。
自動的に２，３、が走らされると、選択の機会が無いか、適当時間を置くとしても
操車者はおちおち選択も出来ない。
自動で走らせられるなら、わざわざコンボを使うことは無いでしょう。
本件は全コンボの選択が終わったら、コマンドボタン（これを前もって作っておく）をクリック
させて、それで本当にやりたいメインの処理が走るようにすべきでは。
コンボのクリック（イベント）も処理確定のキッカケになりうるが、
本質問では、1行多列のデータになるようだが、コンボボックスがすべて選択し終わった
段階で、ボタンをクリックさせて、シートの１行に選択データをセットする、
仕組みにしては。
ーー
ただしイベントモジュールとコントロール作成のモジュール間の変数の
データの受け渡しなどが難しくなるかも。
ーー
たぶん質問者のやりたいことのコードは出来たが、一部納得できない
点があって、ここへ掲出しませんが、上記の点を、質問者がよく考えてください。
ーー
他にわからない点
（１）Cells(2, 16384
の 16384
２００７移行の許容最大列数か。
エクセルバージョンも質問に書いてますか
（２）a = myComboBox.Value
Worksheets(2).Cells(1, 1) = a
は何をやりたいのか
Cells(1, 1）はＡ１セルだけのことだが。