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

質問

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のＡ～Ｅ列の商品のデータが入ってる部分には”商品一覧”と名前を入れてあります。

回答No.3

fumufumu_2006

ANo.3です。
すみません、質問をよく読まなかったので、現在の単価を旧単価にする部分が抜けていました。
また、ANo.3ではApplication.Matchを使いましたが、ANo.2さんのFindの方がいいと思います。

>ControlSourceのプロパティが便利な理由を教えていただけると嬉しいです。
ControlSourceを設定すると、そのTextBoxが、指定したセルへの窓のようになり、テキストボックスの内容を変更すると同時にセルの値が変更され、同様にセルの値を変更するとテキストボックスの値が変わります。
なので、単純な登録や変更なら便利なんですが、今回のような場合では、フォーム上で旧価格と新価格を入れ替える作業を行うと、その時点でセルの内容も変わってしまうので、その回避を考えるとあまりいい方法ではありませんでした。

お礼

ありがとうございました！！
No.２さんのを参考に何とかできました。

そして、ControlSource、便利ですね。
違うところで上手く使えて大満足です。
最後まで相談にのってくださりありがとうございました。

回答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)
を別のボタンクリックの所で使用して行位置を取得しようとしましたが
オブジェクトが指定されていないと、エラーになってしました。

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