[[20220728164132]]　『ユーザーフォームで銀行に対応した支店コードの入』（ケイコ）　ページの最後に飛ぶ

『ユーザーフォームで銀行に対応した支店コードの入力ができません』（ケイコ）

Sheet１のA列に銀行名、B列に銀行コード、C列に支店名、D列に支店コードがあります。
ユーザーフォームのテキストボックス１に銀行名を入力したら、Sheet1を参照し、テキストボックス２に銀行コードが自動入力できるようにはできました。
しかし、テキストボックス３に支店名を入力し、テキストボックス４に銀行に対応した支店コードを自動入力することができません。

下記記述にどのような追加をすれば可能でしょうか？ご指南いただけるとたすかります。

・・・・・・・・・・・・・・・・・・・・・・・・・・
Private Sub 銀行名_Change()
Dim r As Range

Set r = Worksheets("銀行コード").Columns("A:A").Find _
(銀行名.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not r Is Nothing Then
銀行コード.Value = r.Offset(0, 1).Value
Else
銀行コード.Value = ""
End If

End Sub

＜使用 Excel：Office365、使用 OS：Windows10 ＞

 こんにちは

 ＞ユーザーフォームのテキストボックス1に銀行名を入力したら､Sheet1を参照し､
 ＞テキストボックス2に銀行コードが自動入力できるようにはできました｡

 ↑こっちが出来たのなら、落ち着いて考えれば、必ずできます。(もう少し頑張って!!)

（あみな） 2022/07/28(木) 17:09

＞下記記述にどのような追加をすれば可能でしょうか？

ご承知のことと思いますが、Private Sub 銀行名_Change()　と　End Sub　の間に追加してもダメです。

（わからん） 2022/07/28(木) 17:28

■１
＞ユーザーフォームのテキストボックス１に銀行名を入力したら
質問とは関係ありませんが、そういうのは「コンボボックス」や「リストボックス」でやったほうが適切かとおもうのですが如何でしょうか。

 (じゃないと、入力中でもChangeイベント発動しますよね？)

■２
質問のほうは、ワークシート上の話であればXLOOKUP関数などを使えば簡単に求まりますが、マクロのコード上で再現しようとおもうと少々難しいような・・・
すくなくと、支店の【行】を調べるのにFindメソッドを使うのは適切ではないかとおもいます。

 【銀行コード】シートのレイアウト
    __A___   _____B____   ___C__   ___D__
 1  銀行名   銀行コード   支店名   支店コード
 2  A銀行       0001      本店       000
 3  A銀行       0001      支店１     001
 4  A銀行       0001      支店２     002
 5  B銀行       0002      本店       001
 6  B銀行       0002      支店１     002

代案として↑のようなデータのとき↓のようなシート（非表示）を仕込んでおいて、ぱぱっとそちらからデータを持ってくるのではだめなんですか？

 【作業用】シートのレイアウト
   ___A____   ______________________B______________________________
 1  A銀行     =XLOOKUP(A1,銀行コード!A:A,銀行コード!B:B,"該当なし")
 2  支店２    =XLOOKUP(A1&A2,銀行コード!A:A&銀行コード!C:C,銀行コード!D:D,"該当なし")

なお、老婆心ながら、説明するにあたりシート名やオブジェクト名は合わせたほうが伝わりやすいと思います。

（もこな２） 2022/07/28(木) 18:10

まず文字と数値の区別はできているのでしょうか。

（銀行コード、支店コード） 2022/07/28(木) 19:52

コンボボックスを使った方が適切という意見が出ていますが、私もそう思います。
ということでその方針でサンプル作成してみました。

ユーザーフォームに下記のようなコンボボックスとテキストボックスが配置してあるとします。

コンボボックス　銀行名　支店名
テキストボックス　銀行コード　支店コード

【銀行コード】シートのレイアウトは上のもなこ2さんのものと同じとします。

 ユーザーフォームモジュール
 ------------------------------------------------------------
Option Explicit
Dim tbl As Range

 Private Sub UserForm_Initialize()
    Set tbl = Worksheets("銀行コード").Cells(1).CurrentRegion
    Set tbl = Intersect(tbl, tbl.Offset(1))

    With Me.銀行名
        .ColumnCount = 2
        .ColumnWidths = "100;20"
        .ListWidth = 120
        .BoundColumn = 2
        .List = WorksheetFunction.Unique(tbl.Resize(, 2))
    End With

    With Me.支店名
        .ColumnCount = 2
        .ColumnWidths = "100;20"
        .ListWidth = 120
        .BoundColumn = 2
    End With    
End Sub

 Private Sub 銀行名_AfterUpdate()
    If Me.銀行コード.Value <> "" Then
        Me.支店名.List = WorksheetFunction.Filter(tbl.Resize(, 2).Offset(, 2), _
                                           Evaluate(tbl.Resize(, 1).Offset(, 1).Address & "=" & Me.銀行コード.Value))
    End If
End Sub

 Private Sub 銀行名_Change()
    Me.銀行コード.Value = Me.銀行名.Value
End Sub

 Private Sub 支店名_Change()
    Me.支店コード.Value = Me.支店名.Value
End Sub
 ----------------------------------------------------

Office365ということなので、Unique関数やFilter関数が使えるので楽できます。
Filter関数の使い方はちょっと癖がありますね。

（hatena） 2022/07/28(木) 22:16

テキストボックスに入力した後の処理はどのようにしているのだろうか。
（*） 2022/07/29(金) 08:40

コメント返信：

[ 一覧(最新更新順) ]

[[20220728164132]] 『ユーザーフォームで銀行に対応した支店コードの入』（ケイコ） ページの最後に飛ぶ

[[20220728164132]]　『ユーザーフォームで銀行に対応した支店コードの入』（ケイコ）　ページの最後に飛ぶ