ユーザーフォームに別シートからデータを反映させたい。

Question

今回初めてマクロを使って入力フォームを作った初心者です。
教えてください。

TextBox1　お客様番号
TextBox２　お客様氏名
TextBox3　住所

 TextBox1にお客様番号を入れると別シートのデータから ”TextBox２　お客様氏名”と
 ”TextBox3　住所”が反映されるような設定にしたいです。

また、お客様番号を入れて修正ボタンを押すと入力された内容が表示され修正が出来る設定にしたいです。


登録ボタンへのコードは下記の内容になります。

Private Sub CommandButton1_Click()
'登録ボタンをクリックしたときの処理

 Dim LastRow As Long
 Dim MyOpt As String
 Dim i As Long

    
    'ユーザーフォームの内容をExcelに転記
    With Worksheets("接点記録")
        '最終行にプラス1
        LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1

        .Cells(LastRow, 2).Value = Me.TextBox1.Text　　
        .Cells(LastRow, 3).Value = Me.TextBox2.Text
        .Cells(LastRow, 4).Value = Me.TextBox3.Text
        .Cells(LastRow, 5).Value = Me.TextBox4.Text
        .Cells(LastRow, 6).Value = Me.ComboBox1.Text
         .Cells(LastRow, 7).Value = Me.TextBox6.Text
         .Cells(LastRow, 8).Value = Me.TextBox7.Text
        .Cells(LastRow, 9).Value = Me.TextBox8.Text
          .Cells(LastRow, 10).Value = Me.TextBox9.Text
    
        
        TextBox1.Value = ""
        TextBox2.Value = ""
       TextBox3.Value = ""
         TextBox4.Value = ""
         ComboBox1.Value = ""
        TextBox6.Value = ""
        TextBox7.Value = ""
        TextBox8.Value = ""
        TextBox9.Value = ""
      
            End With

   End Sub


ご教示よろしくお願いいたします。

tatsumaru77 · Accepted Answer

下記URLにアップしました。
https://ideone.com/9qF2Ze

使用上の注意
１．見積の項目ですが、こちらで暫定的に登録しておきました。
Me.ComboBox1.AddItem ("comb1")等
UserForm_Initializeのコンボボックスの登録内容をあなたの環境に合わせて、適切に設定してください。

２．お客様番号が０始まりがあるので、
フォーム上で01234567を登録すると、シート上には1234567と登録され、前０がなくなってしまします。
これを防止するために、シートのB列全体を選択し、セルの書式設定で文字列を選択してください。
そうすると、前０が保持されます。

今後の課題
１．入力項目のチェックは、お客様番号のみ行っています。
他の項目は、行っていません。もし、必要であれば、あなたが追加してください。
もし、追加の方法が判らない場合は、その旨補足してください。

２．検索について
現在は、お客様番号に一致するデータのみの検索だけですが、
①お客様番号にワイルドカードを指定しての検索
例　123456** （先頭の６文字が123456の番号）
例　****1234 （最後の４字が1234の番号）
②お客様氏名を指定の検索（ワイルドカードも含む）
などが必要になるかもしれません。
もし、そのような要望があれば、その旨補足してください。

tatsumaru77 · Answer

補足ありがとうございました。

あなたのほうで処理の流れが、まだ決まってないということなので、こちらで考えてみました。
添付図のようにしては、いかがでしょうか。、

ボタンは、
上部に
登録、変更、削除、検索　ボタンを配置します。
下部に
保存、削除実行、中止、閉じる　ボタンを配置します。

処理の流れです。
１．使用場所番号の入力

２．下記のいずれかのボタンのクリック
２．１．登録ボタンのクリック時
　　①該当番号が接点記録シートに存在すれば、エラー表示し、１へ戻る。
　　②該当番号が接点記録シートに存在しなければ、フォームのデータをクリアして、３へ遷移する。
２．２．変更ボタンのクリック時
　　①該当番号が接点記録シートに存在しなければ、エラー表示し、１へ戻る。
　　②該当番号が接点記録シートに存在すれば、接点記録シートのデータをフォームのデータへ転記し、３へ遷移する。
２．３．削除ボタンのクリック時
　　①該当番号が接点記録シートに存在しなければ、エラー表示し、１へ戻る。
　　②該当番号が接点記録シートに存在すれば、接点記録シートのデータをフォームのデータへ転記し、３へ遷移する。
２．４．検索ボタンのクリック時
　　①該当番号が接点記録シートに存在しなければ、エラー表示し、１へ戻る。
　　②該当番号が接点記録シートに存在すれば、接点記録シートのデータをフォームのデータへ転記し、１へ戻る。

３．データの入力
　　フォーム上の使用者氏名～備考を入力、又は、変更する。

４．下記のいずれかのボタンのクリック
４．１．保存ボタンのクリック時（登録、変更の場合）
　　①入力されたデータを接点記録シートへ保存する。
　　②１へ戻る。
４．２．削除実行ボタンのクリック時（削除の場合）
　　①接点記録シートから該当の番号の行を削除する。
　　②１へ戻る。
４．３．中止ボタンのクリック時
　　①１へ戻る。

各ボタンのオブジェクト名は、以下のようにしてください。

登録ボタン：CommandButton登録
変更ボタン：CommandButton変更
削除ボタン：CommandButton削除
保存ボタン：CommandButton保存
削除実行ボタン：CommandButton削除実行
中止ボタン：CommandButton中止
閉じるボタン：CommandButton閉じる

もし、表示を変えたい場合は、Capitionを変えてください。
上記のオブジェクト名でマクロを作成しますので、オブジェクト名は変えないでください。

上記の仕様でいかがでしょうか。

tatsumaru77 · Answer

補足ありがとうございました。
１．お客様番号が正しいということですが、フォーム上では
使用場所番号になっています。
これは、このままでしょうか。それとも、お客様番号に変えるのでしょうか。
使用者氏名も同様です。お客様氏名にするのでしょうか。それとも、このままでしょうか。

２．担当者欄を追加ということですが、
それを追加したフォームを提示していただけませんでしょうか。
（項番1でお客様番号に修正するなら、それも含めて修正したものを提示してください）

３．現在のフォーム上の項目と接点記録シートの項目の対応は以下になっていると把握しています。
もし、間違いがあれば、指摘してください。また、担当者欄が追加になりますので、
それ追加した場合にどうなるかも、提示してください。

項目　　　　　オブジェクト名　　　シートの列
お客様番号　、TextBox1　　　　　　B列
お客様氏名　、TextBox2　　　　　　C列
住所　　　　、TextBox3　　　　　　D列
訪問日　　　、TextBox4　　　　　　E列
見積　　　　、ComboBox1　　　　　　F列
内容　　　　、TextBox7　　　　　　G列
成約日　　　、TextBox8　　　　　　H列
金額　　　　、TextBox9　　　　　　I列
備考　　　　、TextBox10　　　　　　J列

担当者　　　　TextBox?　　　　　　?列

tatsumaru77 · Answer

追加の不明点です。
１．お客様番号と使用場所番号は同じ意味かと思いますが、どちらが正しい用語でしょうか。
２．データの件数は、最大で何件程度になる予定でしょうか。
３．お客様番号は8桁の数字ですが、０から始まる番号もありますか。
例　01234567　等

tatsumaru77 · Answer

処理の流れの確認です。以下の流れであってますか。
１．使用場所番号の入力
２．検索ボタンのクリック
３．検索結果の表示

３．１．番号が接点記録シートに存在しない場合
　①使用者氏名～備考を全てクリア
　②使用者氏名～備考を担当者が入力
　③登録ボタンをクリックし、接点記録シートへ登録する。

３．２．番号が接点記録シートに存在する場合
　①使用者氏名～備考を接点記録シートから読み出し、フォーム上に表示する。
　②使用者氏名～備考の変更箇所を担当者が変更
　③XXボタンをクリックし、接点記録シートへ登録する。
　不明点：このXXボタンは、登録ボタンでしょうか。それとも変更ボタンでしょうか。
　登録ボタンだとすると、検索ボタンはどのような用途を想定していますか。
　変更ボタンだとすると、ボタンの位置は、登録ボタンの横の方が判りやすいかと。

ボタンの番号の確認です。
登録ボタン：CommandButton1
検索ボタン：
変更ボタン：
閉じるボタン：
上記のボタンのオブジェクト名が不明です。提示をお願いします。

その他の確認
①通常の運用で、接点記録シートを直接更新することは、ありますか。
（フォームを経由しないでデータを更新するケース。非常時を除きます。）

②　①と関係しますが、見積は、コンボボックスになっています。
　検索時、使用者番号が接点記録シートに存在し、見積の内容がXXXの時、
　コンボボックスにXXXがあれば、XXXを表示できますが、ない場合は、どうしますか。
　・ない旨のエラー表示をする。表示される内容は、XXX以外のものになる。
　・XXXをコンボボックスに登録し、XXXを登録する。
　　等が考えられます。
　　見積もりの内容を直接書き換えられるとこのケースが発生します。

③　削除ボタンの登録も考えてますか？

fujillin · Answer

No2です。

直接の回答ではありませんけれど・・・

補足で示されたフォームを見てみると、顧客情報の管理と言うよりも契約情報（？）もセットになっているようですが、これらがデータの1セットになっているのでしょうか？

もしそうなら、同一顧客で複数契約とかもありそうですが、どのようにデータを管理するおつもりなのでしょうか？
それとも、「一顧客、一契約」しかありえないので、重複する可能性は絶対にないような内容になっているということなのでしょうか？

全体像をよくわかっていないので、勝手な想像ですけれど・・
・顧客の基本情報は、「顧客情報」として管理し
・契約情報は「契約情報」として管理する
ような内容のような気もしますけれど・・
そうすると、「契約番号」のようなIDが別に必要になりそうな可能性も・・

※ 上記は内容を把握していない者の勝手なつぶやきですので、読み流してくださって構いません。

tatsumaru77 · Answer

補足の画像ですが、画像が不鮮明なので、雰囲気はわかりますが、詳細が判りません。
フォームの部分だけを再度アップしてしただけませんでしょうか。
そうすれば、鮮明になるかと。

tatsumaru77 · Answer

>ユーザーフォームを開いて
>TextBox1にお客様番号を入れると氏名と住所が反映されて

TextBox1にお客様番号を入れると、自動的に、お客様番号で
接点記録シートを検索して、氏名と住所を取得し、
それらをTextBox2,TextBox3に設定する、ということですか？
（お客様番号の入力後、どのボタンもクリックしない前提ですか）

fujillin · Answer

こんにちは

＞TextBox1にお客様番号を入れると別シートのデータから 
＞”TextBox２　お客様氏名”と”TextBox3　住所”が
＞反映されるような設定にしたいです
新規の登録にも使うボックスなのでしょうから、入力されたら常に「検索-表示」というわけでもないように思われます。
（入力すれば常にそのような処理をするのなら、コントロールのUpdate関連のイベントを利用すれば可能です）

例えば「修正」ボタンを押せば、既存データから検索して反映するようにしておいて・・
＞また、お客様番号を入れて修正ボタンを押すと入力された内容が
＞表示され修正が出来る設定にしたいです。
「登録」ボタンを押せば、新規、修正共に登録されるようにしておけば良いのではないでしょうか？

テキストボックス等にデータを反映するには、既存データのＢ列（？）から同じ番号の行を検索して、その行の内容をフォームに表示すれば良いだけです。
（ご提示のコードと、ほぼ逆の処理のような感じ）

新規登録の際に、既存データに上書きしてしまう間違えを防止するのなら、「登録」ボタンを兼用せずに、「新規登録」と「修正登録」のように分けておく方が安全かも知れません。
顧客番号はＩＤなのでしょうから、（自動付加ではないようですので）新規登録の際には番号が重複していないこと等のチェックも必要なのではないかと推測します。
（新規登録なら、番号は自動付加されるという案もありそうな気がしますが・・）

処理がやや複雑になりますけれど、「修正」で既存データを呼び出した際は、「修正登録」が有効で、「新規登録」ボタンは無効になるようにしておき、更には顧客番号の欄も修正（＝入力）不可にしておくとか・・
更には、登録内容の「削除」ボタンも必要なのかもなどと邪推してしまいますが、実際の利用方法の想定がよくわからないので、妄想するのはこのへんで・・

tatsumaru77 · Answer

＞TextBox1にお客様番号を入れると別シートのデータから ”TextBox２　お客様氏名”と”TextBox3　住所”が反映されるような設定にしたいです。

これは、検索ボタンをクリックした時の、動作でしょうか。
表示するのは、
TextBox2
TextBox3
のみで良いのでしょうか。
TextBox4
CommboBox1
TextBox6～TextBox9は表示しなくて良いのでしょうか。

ユーザーフォームに別シートからデータを反映させたい。

下記URLにアップしました。

補足ありがとうございました。

補足ありがとうございました。

追加の不明点です。

処理の流れの確認です。

No2です。

補足の画像ですが、画像が不鮮明なので、雰囲気はわかりますが、詳細が判りません。

>ユーザーフォームを開いて

こんにちは

＞TextBox1にお客様番号を入れると別シートのデータから ”TextBox２ お客様氏名”と”TextBox3 住所”が反映されるような設定にしたいです。

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

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

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

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

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

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

＞TextBox1にお客様番号を入れると別シートのデータから ”TextBox２　お客様氏名”と”TextBox3　住所”が反映されるような設定にしたいです。