ユーザーフォームのテキストボックスに配列の値を読み込む方法を教えて下さい

質問

お世話になります
ユーザーフォームのリストボックスにAddItemで記述したデータを表示するような形で、ユーザーフォームのテキストボックスに配列にとりこんであるデータの値を順番に読み込むようにしたいのですが、このようなことができるのでしょうか？
どなたか教えて頂けないでしょうか

回答No.6

kazuhiko5681 · ベストアンサー

こんにちは。先程のコードでは、コマンドボタンの動作がかかれていませんでしたので、修正マクロをお知らせします。
下記のコードを同じ方法でコピー・ペーストしてみて下さい。
あなた様の思い通りの動作になると思います。

私は、趣味でＶＢＡやＶｂでプログラミングをやっている仲間が集まっているサークルに所属しております。ここでコードの説明をするのは非常に難しいので、事務局のほうへお電話をいただけないでしょうか。お金は要りません。お電話で詳しく教えさせていただきます。
事務局には、午後１時から午前０時迄常時つめておりますので、この時間内であればお電話で話すことができると思います。
０４３(２７８)２５４８

お手数をおかけいたしますが、よろしくお願いいたします。

Private Sub CommandButton1_Click()

If Me.Tx1.Text = Workbooks("顧客台帳.xls").Worksheets(1).Range("A1").Value Then
MsgBox "登録された顧客はおりません。", vbOKOnly + vbCritical, "処　理　エ　ラ　ー"
Else
Me.Tx1.Text = Me.Tx1.Text - 1
Me.Tx2.Text = Workbooks("顧客台帳.xls").Worksheets(1).Range("A1:" & Cells(Rows.Count, 1).End(xlUp).Address).Find(Me.Tx1.Text, lookat:=xlWhole).Offset(0, 1).Value
End If

End Sub

Private Sub CommandButton2_Click()

Dim myCnt1 As Integer
Dim myCnt2 As Integer

If Me.Tx1.Value = Workbooks("顧客台帳.xls").Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Value Then
MsgBox "登録された顧客はおりません。", vbOKOnly + vbCritical, "処　理　エ　ラ　ー"
Else
Me.Tx1.Text = Me.Tx1.Text + 1
Me.Tx2.Text = Workbooks("顧客台帳.xls").Worksheets(1).Range("A1:" & Cells(Rows.Count, 1).End(xlUp).Address).Find(Me.Tx1.Text, lookat:=xlWhole).Offset(0, 1).Value

End If

End Sub

Private Sub Tx1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim myRange As Range

Set myRange = Workbooks("顧客台帳.xls").Worksheets(1).Range("A1:" & Cells(Rows.Count, 1).End(xlUp).Address).Find(Me.Tx1.Text, lookat:=xlWhole)
If myRange Is Nothing Then
MsgBox "登録された顧客がおりません。", vbOKOnly + vbCritical, "入　力　エ　ラ　ー"
Cancel = True: Exit Sub
Else
Me.Tx2.Text = myRange.Offset(0, 1).Value
End If

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

End

End Sub

お礼

kazuhiko5681様
本当にあつかましいお願いにもかかわらず、またまたこのような丁寧なご指導いただき感謝の言葉もございません。本当にありがとうございます。

今回はkazuhiko5681様はじめお二人の方にご指導いただき、得難い勉強をさせていただきました。
まだ始めたばかりで、先がみえませんが今後とも一生懸命勉強していつかは、私も皆様のように人様にアドバイスできるようにがんばりたいと思いますので、今後ともよろしくお願いします。
心より感謝します。
tomosato

回答No.5

maruru01

再びmaruru01です。

標準モジュール

'ユーザーフォームでも使用するので、Publicで宣言。
'データ数が分からないでの動的に宣言。
Public Data1() As String
'データ数
Public DataCount As Long

Sub input1()

'以下の変数はこのプロシージャでしか使用しないので、中に入れます。
'ループカウンタはInteterよりLongの方がいい。
Dim i As Long
Dim j As Long

Sheets("Sheet1").Select

'表形式の場合は、CurrentRegionプロパティでデータ数を取得出来る。
'見出し行の分-1する。
DataCount = Range("B10").CurrentRegion.Rows.Count - 1
'配列の再宣言
Redim Data1(1 To 5, 1 To DataCount)

'（b～f列なら、列番号は2～6ですよね？）
For i = 2 To 6
'最終データ行数は、データ数+開始行-1で取得出来る
For j = 11 To DataCount + 11 - 1
'- 1、- 10は、配列のインデックスが1から始まっているから
Data1(i - 1, j - 10) = Cells(j, i)
Next j
Next i
End Sub

ユーザーフォームのコードモジュール

'宣言部に
Private myCollect As New Colletion　'コレクションオブジェクトの宣言
Private ViewNumber As Long　'現在表示されているデータの行番号

'Initializeイベントに
Private Sub UserForm_Initialize()

Dim i As Long

’コレクションの追加
With myCollect
.AddItem:=text1　'ID
.AddItem:=text2　'氏名
.AddItem:=text3　'部署
・・・略（シートの項目列の順に追加する、もちろん配列の列数と追加するテキストボックスの数は一致すること）
End With

'とりあえず何かデータを表示（とりあえず、先頭のデータ）
ViewNumber = 1
For i = 1 To 5
myCollect(i).Text = Data1(i, 1)
Next i

End Sub

'次のデータへ移動するボタン
Private Sub CommandBUtton1_Click()

Dim i As Long

If ViewNumber = DataCount Then
'最後のデータの場合は移動しない
MsgBox "最後のデータです。"
Exit Sub
Else
ViewNumber = ViewNumber + 1
For i = 1 To 5
myCollect(i).Text = Data1(i, ViewNumber)
Next i
End If

End Sub

'前のデータへ移動するボタン
Private Sub CommandBUtton2_Click()

Dim i As Long

If ViewNumber = 1 Then
'先頭のデータの場合は移動しない
MsgBox "先頭のデータです。"
Exit Sub
Else
ViewNumber = ViewNumber - 1
For i = 1 To 5
myCollect(i).Text = Data1(i, ViewNumber)
Next i
End If

End Sub

なお、こちらの都合で、配列の行と列の順番を入れ替えました。

お礼

maruru01様
身勝手なお願いにもかかわらず、根気よく丁寧にご指導いただき本当にありがとうございます。
おかげさまで、目的をはたすことができました。
maruru01様はじめお二人の方にご指導いただき、今回も書籍では得られない勉強をさせていただきました。
いつかは、私もmaruru01様の1/100でも人様にアドバイスできるようにがんばりたいと思いますので、今後ともよろしくお願いします。
心より感謝します。
tomosato

回答No.4

kazuhiko5681

度々の補足お手数をおかけいたしました。簡単に実現することができます。
早速サンプルマクロを作ってみました。次のように操作してみて下さい。

１.新規ブックを立ち上げ、ＡＬＴ＋Ｆ１１キーを押してＶＢＥ画面を表示させる。
２.ユーザーフォームを１つ挿入し、テキストボックスを２個配置する。
３.テキストボックスのオブジェクト名をTx1.Tx2に変更する。
４.テキストボックスをダブルクリックして、フォームモジュールを開く。
５.Changeイベントのコードが書けるように準備されるので、これを削除し、下記のこーどをコピー・ペーストする。

あらかじめシート１のＡ列にＩＤ・Ｂ列に顧客名を入力しておいて下さい。

ユーザーフォームを立ち上げ、tx1にＩＤを入力するとtx2に自動的に顧客名が入力されます。

もし、不具合等がありましたら遠慮なくお知らせ下さい。あなた様のおやりになりたいことが実現するまで一緒に考えて生きたいと思います。

Private Sub Tx1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim myRange As Range

Set myRange = Workbooks("顧客台帳.xls").Worksheets(1).Range("A1:" & Cells(Rows.Count, 1).End(xlUp).Address).Find(Me.Tx1.Text, lookat:=xlWhole)
If myRange Is Nothing Then
MsgBox "入力されたＩＤは登録されていません。", vbOKOnly + vbCritical, "入　力　エ　ラ　ー"
Cancel = True: Exit Sub
Else
Me.Tx2.Text = myRange.Offset(0, 1).Value
End If

End Sub

お礼

kazuhiko5681様
何度もお手数おかけしました
ご指導のとおり作成しましたところ、見事に作動しました
本当にありがとうございました。
あつかましい限りですが、もう一つお願いがあります
今後の学習のためにプログラムの１行目の(　)内と３行目データ範囲指定をしているところの各コマンドの意味を解説していただけないでしょうか。
ご都合の許す範囲で結構ですので、お聞き届けいただければ幸いです。

回答No.3

maruru01

こんにちは。maruru01です。

まず、コレクションを使ってフォーム上のテキストボックスをインデックスで操作出来るようにします。
コレクションについては以下を参照して下さい。

http://oshiete1.goo.ne.jp/kotaeru.php3?q=172927

この時、AddItemメソッドで追加するテキストボックスの順番をデータ一覧表の項目の順と一致させることが必要です。
例えば配列kokyakudat(i,j)の0列目がIDなら、他のデータは1列目からになりますが、Collectionオブジェクトのインデックス値は1から始まるので、順番（と項目数）が同じならちょうどいいわけです。

で、とりあえず、iが列でjが行で、0列がIDとすると、

For j = (行の最初) To (行の最後)
　　If kokyakudat(0, j) = tx1.Text Then
　　　　'見つけた行のデータを入力する
　　　　For i = 1 to (列番号の最後)
　　　　　　myCollect(i).Text = kokyakudat(i, j)
　　　　Next i
　　　　Exit For
　　End If
Next j

If j > (行の最後) Then
　　MsgBox "データが見つかりません。"
End If

こんな感じです。
ところで、このようなデータは配列より、ユーザー定義型（構造体）にした方がいいのではないでしょうか。
構造体なら、各項目に適当な変数名を付けることが出来るので、コードの見た目で分かり易いです。
配列だと、何番目が何のデータかコード見ただけでは分からないですよね。
まあ、構造体にすると上記のコレクションは使えません（使っても意味がない）が、項目数がそれ程多くない（20以下ぐらい）なら、1つずつ入力してもいいでしょう。
テキストボックスにも分かりやすい名前を付けておけば、何のデータをどこにいれたかが、確認し易いですし。
ちなみに、この手のデータ検索は、Accessを使用した方がいいと思いますが。

参考URL：

http://oshiete1.goo.ne.jp/kotaeru.php3?q=172927

補足

maruru01様
いつもお手数おかけして申し訳ありません
相変わらず丁寧なご指導に感謝します
私の不勉強のため、せっかく丁寧にご指導頂いたにもかかわらず、内容が理解できないところが多数あります。
本来ならば、勉強し直してから再度お尋ねするのが筋だと思いますが、今回のユーザーフォームの件はどうしても解決してみたいので、あつかましいかぎりですが、現在作っているマクロの概要を以下に記載し、どこをどのようになおすべきか再度おたずねする次第です。
とりあえず、IDを入力し、氏名が表示できれば、後は自分で何とかしたいと思います。
　お時間とお気持ちの許す範囲で結構ですので見ていただければ幸いです

　エクセルのsheet1のb10～f10にID,氏名,部署のフィールドがあり、その下に500件程のデータが入力済みです。

マクロの記述の概要は以下のとおりです

Dim data1(1000,10)
Dim i As Integer
Dim j As INteger

Sub input1()

Sheets("Sheet1").Select
Range("b11").Select

'データを配列に格納
For i = 11 To 1000
If Cells(i,0) = "" Then Exit For
For j = 1 To 7
data1(i, j) = Cells(i, j)
Next i
Next j

UserForm1.Show

End Sub

ユーザーフォームの概要とマクロ

UserForm1

TextBox1・・・ID入力欄
TextBox2・・・氏名をここに表示させたい
TextBox3・・・部署をここに表示させたい

　CommandBUtton1・・・・次のIDへ移動させたい
CommandButton2・・・・前のIDへ移動させたい

ユーザーフォームのマクロ
*をつけたところが、maruru01さんのご指導を私なりに理解して加えた部分です（たぶん理解ができていないと思いますが）

*Private myCollect As New Collection・・・変数の宣言部分？
Private Sub UserForm_Click()

*With myCollect・・・・コレクションの作成?
* .Add Item:=text1
* .Add Item:=text2
　 *.Add Item:=text3
*End With

*For i = 11 to 1000
* If data1(i,1) = tx1.Text Then
* For j = 1 to 7
* myCollect(j).Text = data1(i,j)
* Next j
* Exit For
* End If
*Next i
* If i>1000 Then
* MsgBox = "データなし"
* End If

End Sub
Privare Sub CommandButton1_Click()
未作成
End Sub

回答No.2

kazuhiko5681

早速の補足有難うございます。まだよくあなた様のおやりになりたいことが理解できません。ユーザーフォームの構成を教えていただけないでしょうか。それとＶＢを使うのでしょうか。それともＶＢＡを使うのでしょうか。その点についても教えて下さい。
お手数をおかけいたしますが、よろしくお願いいたします。

補足

kazuhiko5681様
ご親切感謝します。
現在エクセルVBAを学習中です
実際に業務で使用できるマクロを作成しながら勉強しようと考えています。
　現在、エクセルに入力してあるお客様のIDと氏名、購入商品等のデータ一覧表をfor～nextでkokyakudat(i,j)という配列に読み込ませてあります。
　いままで、データ入力を直接エクセルの一覧表に入力していたのですが、今度、入力専用のユーザーフォームを作り、ここから入力したいと考えました。
　作成したユーザフォームの構成は、ユーザID入力用にテキストボックス１(tx1)、氏名表示用にテキストボックス２(tx2)、以下購入済み製品を表示するボックスがあります。
　このTX1にユーザIDを入力すると、このIDを判別してtx2にkokyakudat(i,j)にある氏名を自動的に読み込むようにできないだろうかということなのですが、可能でしょうか
　VBAについて初心者ですので、テキストボックスは入力専用であり、フォーム外のデータを読み込み表示するボックスが別にあるのか、それすらも判断できないまま質問しております。申し訳ありません
　お手数ばかりおかけしますが、ご指導いただければ幸いです

回答No.1

kazuhiko5681

あなた様のおやりになりたいことを具体的に教えていただけないでしょうか。いい方法をアドバイスできるかと思います。
お手数をおかけいたしますが、よろしくお願いいたします。

補足

kazuhiko5681様
わかりにくい質問で申し訳ありません。具体的にやりたいことを述べるべきでしたやろうとしていることは、ユーザーフォームでお客様のＩＤナンバーを入力すれば
あらかじめ配列に読み込んであるお客様の氏名や各情報をユーザーフォームに表示し、新しいデータを入力するといったことをやりたいのですが可能でしょうか
ちょうどエクセルのデータのフォームという機能をユーザーフォームでやりたい
ということなのですが
よろしくお願いします