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

Question

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にしてるのは、
数値・文字列として書き換えの必要がないので動かせないほうがいいのでは
とういう個人的な思い込みからですので、特にこだわりはございません。

相当な初心者のため、少しばかり注釈をつけてくださる大変ありがたいです。
上記のよう順番でなくても、よい方法があれば教えて下さい。
よろしくお願い致します。

redfox63 · Accepted Answer

コードのテキストボックスの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

# エラー処理を何もしていないので 適宜書き加えてください
# 日付がありえない日付であるとか、単価が金額として意味を成さない文字列であるとか
# 必要な箇所のデータが入力されていないなど

fumufumu_2006 · Answer

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

fumufumu_2006 · Answer

こんな方法はどうでしょうか？

まず、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を設定すると指定したセルをダイレクトに変更するため、全部入力してから[変更実行]みたいな事ができません。

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

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

この回答への補足

ANo.3です。

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

この回答への補足

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング