エクセルの入力規則で質問です。

顧客リストのようなものをつくっています。
セルへの入力でプルダウンリストを使いたいのですが、既存の顧客についてはプルダウンリストから選択する。
そして、新規の顧客がいる場合にはプルダウンリストではなくて直接入力をセルにしたいです。
さらに、入力済みの新規顧客については2回目以降はプルダウンリストに反映させたいのです。

このような入力規則の設定の仕方はあるでしょうか。
関数を使って、プルダウンリストの元データへ入力済みの顧客データをリンクさせるようにする方法は思いついたのですが、すっきりとした方法が思いつきません。
簡単なVBA等で解決する方法があれば教えて下さい。

回答の条件
  • URL必須
  • 1人2回まで
  • 登録:2009/03/31 12:31:20
  • 終了:2009/03/31 22:31:57

回答(2件)

id:SALINGER No.1

SALINGER回答回数3454ベストアンサー獲得回数9692009/03/31 13:01:55

ポイント50pt

VBAのWorksheet_Changeイベントを使って、こんな感じでどうでしょうか。


プルダウンリストはA1セルに、顧客名簿はB列にしました。

それぞれ、"A1"と"B"の部分を変更することで実際の環境に合わせてください。


最初はプルダウンリストが無いので、A1セルに何か文字を入れてイベントを実行させると、プルダウンがでます。

Validation.ShowError = False

とすることで新規顧客を登録できるようにしています。


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    Dim str As String
    Dim r As Range
    Dim lastRow As Long
    Dim f As Boolean
    
    'ドロップダウンリストのセルを指定
    Set r = Range("A1")
    
    If Intersect(Target, r) Is Nothing Then Exit Sub
    If Target.Count <> 1 Then Exit Sub
    
    '顧客リストの最終行の取得
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    '新規かどうかを判断
    For i = 1 To lastRow
        If Cells(i, "B").Value = Target.Value Then
            f = True
            Exit For
        End If
    Next
    
    If f = False Then
        '新規顧客の追加
        Cells(lastRow + 1, "B").Value = Target.Value
        
        'リストを削除
        r.Validation.Delete
            
        'リストの文字列の作成
        str = ""
        For i = 1 To lastRow + 1
            str = str & Cells(i, "B").Value & ","
        Next i
        
        'リストの設定
        With r.Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=Left(str, Len(str) - 1)
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .IMEMode = xlIMEModeNoControl
            .ShowInput = True
            .ShowError = False
        End With
    End If
End Sub

http://q.hatena.ne.jp/

id:automatic214

ありがとうございます。これを加工することでできそうですね。

2009/03/31 22:30:12
id:tap_t No.2

たっぷ回答回数45ベストアンサー獲得回数62009/03/31 15:37:09

ポイント20pt

同じ列にある、顧客名などを対象にすると書いてした場合、最も簡単な方法としては、

入力するセルで右クリック -> リストから選択 -> 入力されている項目のリストが表示される。

もしくは、入力するセルで、ALT + 下矢印キーでも同様です。

今まで、入力していないものの場合は、通常通り入力します。

http://q.hatena.ne.jp/answer

id:automatic214

なるほど。意外とこの方法は盲点でした。ありがとうございます。

2009/03/31 22:30:34
  • id:Mook
    コメントが無効でしたので、終了後で失礼します。

    顧客数はリストで管理できる程度の数で収まるのかという疑問はありますが、
    選択リストに名前を使用すると、選択セル自身の変更は行わなくても済みますし、
    選択リストは別シートにおくこともできます。
    http://www.relief.jp/itnote/archives/000210.php

    入力セルは名前を使ってリストを設定し、新規項目が入力されたときだけ参照先の
    範囲を修正するようにすれば、入力個所が複数あるようなケースにも対応できますし、
    状態もシートで確認できますので、便利な気がします。

  • id:SALINGER
    方法を説明するために便宜的に同一シートのB列を使いましたが、
    別のシートにリストを作ってももちろん問題はありません。
    ドロップダウンリストにリストを設定するのですから、当然大規模なリストではないと想像できますが、
    「簡単な」とありますので、tap_tさんのオートコンプリートのドロップダウンリストで用が足りるのであれば
    それに越したことはありません。


    余談ですが、ドロップダウンリストが複数ある場合でも
    r.Validation

    With r.SpecialCells(xlCellTypeSameValidation).Validation
    とすることで、一括してリストを変更することが可能です。
    また、リストの状態?を確認することも当然可能です。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません