エクセルのフォームに値を返す方法について

質問

バーコードリーダでバーコードを読み、それに該当する商品を商品リストから探し出して表示させたいと考えています。

【エクセルのフォームの概要】
(1)バーコードNo用の「textbox1」→txtbox1
(2)商品表示用の「textbox2」→txtbox2
(3)商品リストのA2～E1000までデータが入っていて、商品名はその一覧表の左から3番目です。

見よう見まねで色々検討しているのですが、どうしてもできません。下記のように考えていますが、何も表示されません。ご指導の程宜しくお願い致します。

Dim RangeA As Range
Dim Ansform As String
Set RangeA = Worksheets("商品リスト").Range("A2:E1000")
On Error Resume Next
Ansform = Application.WorksheetFunction.VLookup(txtbox1.Value, RangeA, 3, False)
txtbox2.Text = Ansform

初めてマクロ（VBA)の世界に入ってきたため、さっぱりわかっていません。突然組みだしてもさっぱり前に進まないので、バーコードリーダーを使った商品管理をエクセルでするような事例がどこかのサイトに出ていましたら教えていただけませんか？（検索をかけてもうまく見つかりません。）

回答No.5

imogasi · ベストアンサー

Sheet1にテキストボックスを２つ貼り付けて実験しました。
Sheet１のシートモジュールのTextBox1のKeyDownイベントに
（編集モードでダブルクリックして、GeneralをTextBox1、
DeclalationをKeydounを選ぶ）
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
Dim RangeA As Range
Set RangeA = Worksheets("商品リスト").Range("A2:E1000")
On Error Resume Next
TextBox2.Text = Application.WorksheetFunction.VLookup(Val(TextBox1.Value), RangeA, 3, False)
End If
End Sub
商品リストのA2:E1000に（B列と第５行以下行は省略）
1234 お菓子
1235 せんべい
1236 あめ
テキストボックス１で「１２３４」を入れてENTERキーを押すと
テキストボックス２が「お菓子」と出ました。
ただバーコードリーダーから入るデータが、Enter押しと同じか自身が無いが。
この質問の解決は、ご回答１－３の路線に疑問を持っている。イベントと結び付けないとダメなのでは。
そうであれば、質問者にとっては、VBで熟達しているなら別ですが、手に余る質問と思う。問題として中級以上の質問でしょう。
ーー
追記
UserForm１上にテキストボックスを２つ貼り付けてやってみても
同じコードで同じ結果になりました。

お礼

imogasi様
ありがとうございます！！　何とできました！！　
バーコードリーダでバーコードを読み込み、その該当商品を表示することができました。
> Sheet１のシートモジュールのTextBox1の
> KeyDownイベントに
>（編集モードでダブルクリックして、Generalを
> TextBox1、DeclalationをKeydounを選ぶ）
という部分が何のことかわからなかったのですが、とりあえずimogasi様のプログラムを貼り付け実行したところ、できました！！
頭の中で構想を練っている段階ではとても簡単にできそうな感じだったのですが、実際にやってみるととてつもなく困難なものでした。
「手に余る質問」・・・本当にその通りでした。
でも、皆様のお力をお借りして、一つのハードルをクリアすることができました。
これからもわからないことが多々出てくると思います。その時はまた質問させていただきますので、どうぞ宜しくお願い致します。
ありがとうございました。

回答No.4

hana-hana3

＞13桁の数値を入力しているのですが、1桁目までしか読んでいませんでした。

コードがどこに書かれているのか解かりませんが、textbox1.change イベントに書かれているなら、１文字目が送り込まれた時点で反応している可能性があります。

BeforeUpdateなど、別のイベントに書いた方が良いでしょう。

＞バーコードリーダでバーコードを読んで、その値に該当する商品を同一フォーム上に表したいと言うことです。

このデータをテキストボックスで確実に受取れる事は確認出来ているのですか？

お礼

hana-hana3様
何度も御回答頂き、本当にありがとうございます。
どうしても動かなかったのですが、No.5の方の御回答に従い挑戦してみましたところ、無事表示させることができました。
本当にありがとうございました。

回答No.3

hana-hana3

＞・・・どうも動きません

txtbox1.Valueに値は入っていますか？
その値は、商品リストにあるものですか？

Vlookupで一致しない値（データに無い値）の場合はエラーになります。

>On Error Resume Next
で、システムからのエラーメッセージを回避する事になります。
エラーの時に、Ansformは以前の値（この場合は宣言された後なので値が入っていない）が入っています。

そのため『下記のように考えていますが、何も表示されません。』と言う状態になっていたのではないでしょうか？

お礼

たびたびありがとうございます。
何で動かないのでしょう？

値は入力しています。
商品リストに入っている値を入力しています。
On Error～は削除しました。
・・・
色々いじって試しているのですが、ダメです。
今回、プログラムを書きましたが、もっと簡単な方法や確実に動く方法があれば教えていただけませんか？

したいことはバーコードリーダでバーコードを読んで、その値に該当する商品を同一フォーム上に表したいと言うことです。
是非、御教示の程、宜しくお願い致します。

補足

txtbox1.Valueの値を再確認しました。
13桁の数値を入力しているのですが、1桁目までしか読んでいませんでした。
どうすれば13桁目まで読んでくれるのでしょうか？
もしかしたら、ここに最大の原因があったのかもしれません・・・・。
色々申し訳ございません。

回答No.2

hana-hana3

＞フォームのテキストボックスに商品名を書き出したいと考えています。

フォームのモジュール書かれているならこのままで動作するはずです。
標準モジュールや別のフォームに書かれているなら、
フォーム名.Textbox2　になります。

お礼

hana-hana3様
再度の御回答ありがとうございます。
「フォームのモジュール」に
Worksheets("商品マスター").Activate
という一文を加えて、
Ansform = Application.WorksheetFunction.VLookup(UserForm1.textbox1.Text, RangeA, 3, False)
UserForm1.textbox2.Text = Ansform
として再挑戦しているのですが、・・・どうも動きません。
デバッグをすると、Ansformの行が黄色く表示されていますが、どこをどう直せばよいのかわかりません。
申し訳ございませんが、再度のご指導、宜しくお願い致します。

補足

hana-hana3様
エラー番号は1004で、「WorksheetFunctionクラスのVLookupプロパティを取得できません。」となっています。
「Range オブジェクトの一部のメソッドでは、対象となる範囲にデータが入力されている必要があります。」とヘルプに書かれていましたので、とりあえずデータの入れてある範囲（Ａ２：Ｅ１６）として再挑戦してみましたが、やはりダメでした。

回答No.1

hana-hana3

＞txtbox1.Value
＞txtbox2.Text

では、どこにあるものかわかりません。
Sheets("シート名").txtbox1
のように指定してください。

＞On Error Resume Next

デバッグ時にこれがあるとエラー箇所がわかりません。
コメントアウトしておきましょう。
そうすればエラーが発生すると実行が止まります。

お礼

hana-hana3様
早速御回答いただきましてありがとうございます。

恐れ入ります。私の説明がわかりづらかったと思います。
シートに商品名を書き出すのではなく、フォームのテキストボックスに商品名を書き出したいと考えています。
・・・ということは、フォームのテキストボックスだと言うことを指定しなくてはいけないようですが、どうにも・・・わかりません。
再度ご指導の程宜しくお願い致します。