ユーザフォームを使ってのデータの書き換え（エクセル）

質問

sheet1に下記のように商品のデータ一覧が入っており、
価格の改定があった時に[単価]に新しい価格をいれ、
当初[単価]に入っていた価格を[旧単価]に入れるようを作っています。

コード　商品名　 単価　　旧単価　　改定日
1111　　商品A　　1000
1112　　商品B　　1500
1113　　商品C　　1200

↓

コード　商品名　 単価　　旧単価　　改定日
1111　　商品A　　1100　　1000　　　2008/5/2
1112　　商品B　　1500
1113　　商品C　　1200

<UserForm>
コード　[コードのTextBox]
商品名[商品名のLabel]
単価　 [単価ののLabel]
新単価[新単価のTextBox]
改定日[改定日のTextBox]

ユーザーフォムでテキストボックスにコードを入れたら、
コードを商品のデータ一覧から検索して商品名と単価を
ユーザーフォムのLabel Captionに自動で表示されるようにしたいのです。

１．コードの入力
２．商品名、単価が表示される
３．新単価、改定日の入力
４．元のデータ（Sheet1）の書き換え

上記のような順序で考えていたのですが、
どうしてもコード入力からの検索表示が上手くいかないのです。
どうすれば良いのでしょうか？

※ユーザーフォームで[商品名][単価]をLabelにしてるのは、
数値・文字列として書き換えの必要がないので動かせないほうがいいのでは
とういう個人的な思い込みからですので、特にこだわりはございません。

相当な初心者のため、少しばかり注釈をつけてくださる大変ありがたいです。
上記のよう順番でなくても、よい方法があれば教えて下さい。
よろしくお願い致します。

回答No.1

redfox63 · ベストアンサー

コードのテキストボックスのChangeイベントで検索すればいいのでは

たとえば
コードテキストボックス : CodeText
新単価テキストボックス : NewPriceText
改定日テキストボックス : UpdateDateText
商品名ラベル　　　　　 : NameLabel
単価ラベル　　　　　　 : PriceLabel
データの記述してあるシート: 商品マスター
といった名前だとします

Sub CodeText_Change()
　　' 検索二一致したセルを記憶する変数
　　dim r as Range
　　Set r = Worksheets("商品マスター").Range("A:A"). _
　　　　Find( CodeText.Text, MatchCase = False )
　　if r is nothing then
　　　　' 検索したが コードが見つからなかった場合
　　　　Name.Label.Caption = "---"
　　　　PriceLabel.Caption = "---"
　　　　NewPriceText.Text = ""
　　　　UpdateText.Text = ""
　　else
　　　　' 該当コードがある場合
　　　　' 見つかったセルの隣のセルの内容をコントロール設定
　　　　' この場合に Rangeオブジェクトの Offsetメソッドで指示
　　　　Name.Label.Caption = r.Offset(0,1).Value
　　　　PriceLabel.Caption = r.Offset(0,2).Value
　　　　NewPriceText.Text = r.Offset(0,2).Value
　　　　UpdateText.Text = r.Offset(0,4).Value
　　end if
End Sub

といった具合で検索出来るともいます

改訂 などのボタンを準備しておいて
Sub Kaitei_Click()
dim r as Range
　　Set r = Worksheets("商品マスター").Range("A:A"). _
　　　　Find( CodeText.Text, MatchCase = False )
　　if r is Nothing then
　　　　MsgBox "コードが見つかりません"
　　　　Exit Sub
　　end if
　　' 単価を更新
　　r.Offset(0,2).value = NewPriceText.Text
　　' 旧単価列を更新
　　r.Offset(0,3).Value = PriceLabel.Caption
　　' 改訂日を更新
　　r.Offset(0,4).Value = DateValue( UpdateText.Text )
End Sub

# エラー処理を何もしていないので 適宜書き加えてください
# 日付がありえない日付であるとか、単価が金額として意味を成さない文字列であるとか
# 必要な箇所のデータが入力されていないなど

お礼

何度か試行錯誤してきちんと指定ができていないのを発見しました。
ありがとうございました。

補足

素早いご返答ありがとうございます。
まさに改定ボタンを作って書換えしようと試みておりました。
そのため、検索するコードの行位置が欲しかったのです。

セル位置を検索して、Offsetを使うことは大変勉強になりました。

教えていただいたのをコピーして試したところ
　　Set r = Worksheets("商品マスター").Range("A:A"). _
　　　　Find( CodeText.Text, MatchCase = False )
のところで"型が一致しません”というエラーになるのです。

商品の追加のsheet1の商品のコードは数値の指定をして入ってる為かと思い、
Find の後の「CodeText.Text」をValやCIntやCLngでくくってみましたがどれも失敗しました。
どうしたらいいのでしょうか？

またChangeイベントでコードが見つからなければ、変更のユーザーフォムは終了なのですが、
一文字入力するごとにイベントが発生するわけですからメッセージをだすと面倒になりますよね？

変更する商品のコードが見つからなければ、
商品追加のユーザーフォームをロードできたらと思っているのですが、
メッセージボックスと同じ理由で難しいのかなとあきらめています。

まずは、最初の所を何とかしなければなりませんのでお力を貸してください。
よろしくお願い致します。

※補足ですが、sheet1のＡ～Ｅ列の商品のデータが入ってる部分には”商品一覧”と名前を入れてあります。