簡易リストボックス

Question

http://oshiete.goo.ne.jp/qa/8161154.html
で質問したものです。
行で分けたのですが、は行が２００近くあり、行数だけでは難しいとおもい、
ＶＢＡでやる頭２文字でリストが表示できるようにしたいとおもいます。
いろいろと調べたのですが、
http://www.max.hi-ho.ne.jp/happy/YNxv98314.html
が参考になるかとおもったのですが、ＶＢＡは全然わかりません。
頭文字を頭２文字に変更して教えていただけますでしょうか？

tom04 · Accepted Answer

こんばんは！
一案です。

↓の画像で説明します。
左側がSheet1でリスト表示させたいSheet
右側がSheet2でリストデータがあるSheetだとします。
Sheet1のA列にリスト候補を絞ってリスト設定させるとし、
Sheet2のA列にすべてのリストデータがあり、
Sheet1のA列のリストとして表示させたいデータだけをSheet2のC列に表示させます。

まず　Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面に
↓のコードをコピー＆ペーストしておいてください。

Sub リスト()    'この行から
    Dim i As Long, cnt As Long, wS1 As Worksheet, wS2 As Worksheet
    Set wS1 = Worksheets("Sheet1")  '←Sheet1は実際のSheet名に！
    Set wS2 = Worksheets("Sheet2")  '←Sheet2も実際のSheet名に！
    i = wS2.Cells(Rows.Count, "C").End(xlUp).Row
    Application.ScreenUpdating = False
    If i > 1 Then
        Range(wS2.Cells(2, "C"), wS2.Cells(i, "C")).ClearContents
    End If
    cnt = 1
    If Selection.Column = 1 And Selection.Count = 1 Then
        For i = 2 To wS2.Cells(Rows.Count, "A").End(xlUp).Row
            If wS2.Cells(i, "A") Like Selection & "*" Then
                cnt = cnt + 1
                wS2.Cells(cnt, "C") = wS2.Cells(i, "A")
            End If
        Next i
        Application.ScreenUpdating = True
    End If
End Sub 'この行まで

つぎに画面左下のSheet1にSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペースト

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    'この行から
   If Target.Column = 1 And Target.Count = 1 Then
    Call リスト
    End If
End Sub 'この行まで

最後にSheet1のA列のリスト表示させたいセルを範囲指定　→　データ　→　データの入力規則　→　元の値の欄に
=OFFSET(Sheet2!C$1,1,,COUNTA(Sheet2!C:C)-1)
という数式を入れます
同じ画面で「エラーメッセージ」タブ　→　「無効なデータが入力されたら・・・」のチェックを外しておきます。

以上で完了です。

※　Excel2010以降ではこの数式が使用できますが、
Excel2007までのバージョンだと数式で別Sheetを指定できないかもしれません。
その場合はSheet2のC列を適当に範囲指定　→　「名前定義」しておき　→　元の値の欄に
=「名前定義した名前」
とします。
この場合、行数が指定できませんので多目に範囲指定しなくてはなりません。

尚、オートコンプリート機能のようなものではありませんので、
Sheet1のA列に1文字以上を入力　→　Enter　→　先ほどのセルに戻る
という操作が必要です。

参考になりますかね？m（_ _）m

MarcoRossiItaly · Answer

「簡易リストボックス」とかいうものをリストの機能で作られたいそうなので、作業列をたくさん使ったらどれだけ数式が易しくなるか試してみましたが、それほど易しくもなりませんでした。リストから選ぶユーザーにとっては簡易だとしても、それを構築するのはまた話が別なのですね。

J3 セルに入力する文字数は、何文字でも構いません。ただ平仮名か片仮名か、振り仮名の設定に合った種類の文字を J3 に入力しないと、機能しません。

Sheet1

B5　=phonetic(b5)
D5　=left(c5,len(sheet2!J$3))
E5　=(d5=sheet2!J$3)*(n(e4)+1)
F5　=e5+9999*(e5=0)
G5　=row(g5)*(e5=min(F$5:F$14))
H5　=row(h5)*(e5=max(E$5:E$14))

Sheet2

J3
ゆうき

「名前の定義」の「参照範囲」ボックス
=offset(sheet1!b5,max(sheet1!g5:g14)-row(sheet1!b5),0,max(sheet1!H5:H14)-max(sheet1!g5:g14)+1)

L3の入力規則　リストの「元の値」ボックス
=絞り込み範囲　　……上で「絞り込み範囲」という名前を付けた場合

ところで、は行だけで 200 近い入力候補があるということは、全体では 1,000 にも迫る個数でしょうか？よかったら、後学のために、それほど大量なリストを使うニーズというのはどんな状況なのか、差し支えない範囲で教えていただけませんか？

tom04 · Answer

No.2です！

補足の件ですが・・・

サイトの画像を拝見するとExcel2013をお使いのようですので、
リストの数式もそのまま利用できるみたいですね！

「データの入力規則」のダイアログ画面を見ると
「入力値の種類」が「ユーザー設定」になっていますね！

「リスト」を選択して、数式欄をそのままの数式でちゃんと動作すると思います。

※　前回も書きましたが、その画面の「エラーメッセージ」タブで
「無効なデータが入力されたら・・・」のチェックを外すのを忘れないでください。m(_ _)m

tsubuyuki · Answer

ＶＢＡにこだわっていらっしゃるようですが・・
一般機能だけでもできますよ。

> ＶＢＡは全然わかりません。
とのことですから、一応提案しておきます。

添付図に沿って話を進めます（実際の構成に従って適宜変更ください）。

まず準備として、
Ａ列に「実際のリスト（全部の一覧）」を置いておきます。
「頭文字として認識させるための文字」をＧ１セルに入力するものとし、
絞り込まれた「入力規則によるリスト」を入力するセルをＧ３セルに置いてあります。
（Ｇ２セルはオマケで、絞り込まれた件数が入っています。）

Ｂ列に作業列を作ります。
Ｂ１セルに
　　=IF(IFERROR(FIND($G$1,A1,1)=1,0),ROW(),"")
　　※２００３以前なら
　　　　=IF(ISERROR(FIND($G$1,A1,1)),"",IF(FIND($G$1,A1,1)=1,ROW(),""))
として、行方向にフィルします。
ザックリと「Ｇ１セルの文字から始まるものに番号を振る」列です。

Ｄ列も作業列です。
Ｄ１セルに
　　=IF(COUNT(B:B)<ROW(A1),"",INDEX(A:A,SMALL(B:B,ROW(A1))))
として、行方向にフィルします。
要するに、Ｂ列に番号が振られた行のＡ列を返してきます。
これで、抽出された（絞り込まれた）リストは完成です。

次に、動的な名前を付け、入力規則にリストとして反映させる準備です。
２００７以降の場合、リボンの数式タブ⇒名前の定義⇒名前の定義
２００３以前なら、挿入メニュー⇒名前⇒定義
で名前の定義ダイアログを出し、
　　名前：絞込リスト　　　　（任意。なんでも良いです。）
　　参照範囲：=OFFSET(Sheet1!$D$1,0,0,COUNTIF(Sheet1!$D:$D,">"""""),1)
とし、ＯＫで終了します。

最後。
入力規則を設定します。ダイアログを出す手順はお分かりのようなので割愛。
　　入力値の種類：リスト　　　
　　元の値：=絞込リスト　　　　　（先ほどつけた「名前」）
として、ＯＫで終了します。

オマケとして、（必要なら）いくつまで絞り込まれたか表示するためにＧ２セルに
　　=COUNTIF($D:$D,">""""")
として、終了です。

ちなみに、図では「頭二文字」としていますが、
実際には１文字だろうが３文字だろうが１０文字だろうが絞り込めます。
リストの範囲を「定義済みの名前」にしていますので
リスト・絞込リストが別シートにあっても対応できます。

わからないＶＢＡコードをわからないまま使うと、
あとで変更（リストを列が変わってみたり）が入った時に痛い目に合います。

とりあえず、参考までに。

hallo-2007 · Answer

一例です。
仮に　D列にデータがあいくえお順にあるとします。
A2　セルに　はあ　とか絞り込み値を入れます。
C列に　=IF(ROW(A1)>COUNTIF(D:D,A$2&"*"),"",INDEX(D:D,MATCH(A$2,D:D,1)+ROW(A1)-1))
と入れてたっぷりと下までコピーします。

C列に　A2で指定した　文字で始まるデータが表示されますので
このC列を、入力規則のリストの範囲に指定します。

別案ですが
データに見出しの文字を追加しておきます。
あ
味
雨
か
紙
・・・
は
歯
はか
墓
・・・
とかにします。
入力規則のセルに見出しの文字を入れると、指定したところが一番上に
くるので、選択が簡単になります。

簡易リストボックス

こんばんは！

この回答への補足

「簡易リストボックス」とかいうものをリストの機能で作られたいそうなので、作業列をたくさん使ったらどれだけ数式が易しくなるか試してみましたが、それほど易しくもなりませんでした。

No.2です！

ＶＢＡにこだわっていらっしゃるようですが・・

一例です。

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

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

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

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