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

質問

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.2

fumufumu_2006

こんな方法はどうでしょうか？

まず、LabelはやめてすべてTextBoxにします。
コード[TextBox1]
商品名[TextBox2]
単価　[TextBox3]
新単価[TextBox4]
改定日[TextBox5]
だとします。
これはControlSourceという便利なプロパティを使うためです。
一応、商品名[TextBox2]と単価[TextBox3]は、編集禁止と自動的に移動しなくなるようにします。
商品名[TextBox2]と単価[TextBox3]のプロパティで、
Locked=True
TabStop=False
にします。
データがSheets("Sheet1")にあるとします。
後は以下のコードをユーザーフォームのモジュール部にコピーしてください。
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim r As Variant '検索結果の行（エラー対応のためVariantにする）
With Sheets("Sheet1")
'TextBox1の値をASheet1のA列で探す
r = Application.Match(Val(TextBox1.Value), .Columns(1), 0)
'A列に見つからない場合
If IsError(r) Then
'エラーメッセージ
MsgBox TextBox1.Value & " はありません"
'TextBox1の文字列を全選択状態にする
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
'更新中止
Cancel = True
Exit Sub
End If
'各TextBoxのValueプロパティにリンクさせるセル（ControlSource）を設定(シート名付きで)
TextBox2.ControlSource = .Name & "!" & .Cells(r, 2).Address
TextBox3.ControlSource = .Name & "!" & .Cells(r, 3).Address
TextBox4.ControlSource = .Name & "!" & .Cells(r, 4).Address
TextBox5.ControlSource = .Name & "!" & .Cells(r, 5).Address
End With
End Sub

p.s.
ControlSourceを設定すると指定したセルをダイレクトに変更するため、全部入力してから[変更実行]みたいな事ができません。

補足

素早いお返事ありがとうございます。
ControlSourceのプロパティが便利な理由を教えていただけると嬉しいです。

おっしゃるように表示はこれでできるようになりました。
Tabキーで入力項目に行くときに表示されました。

ユーザーフォームへは希望の値がテキストボックスに返ってくるので、そのまま下に
Sheets("Sheet1").Cells(r, 3) = TextBox4.Text
Sheets("Sheet1").Cells(r, 4) = TextBox3.Text
Sheets("Sheet1").Cells(r, 5) = TextBox5.Text
と付け足したのですが、旧単価と新単価をリンクとは入れ替えて表示したいため無理でした。

ですので、r = Application.Match(Val(TextBox1.Value), .Columns(1), 0)
を別のボタンクリックの所で使用して行位置を取得しようとしましたが
オブジェクトが指定されていないと、エラーになってしました。

頂いた回答を参考にしてなんとかならないものかと自分なりやっては見たのですが
やはり知識が少なく上手くいきません。
なにか他にアレンジできる方法があれば教えて下さい。
よろしくお願い致します。