プルダウンでなくてユーザーフォームで選ぶ(プルダウンの文字数が多い場合)
こんにちは。
派犬事務員のコロ子です。
以前書いた「連動したプルダウンを作る」の記事に「リストに入れる文字数が多くなるとVBAが壊れる現象が起きる」というコメントを頂いて、調べてみたら、プルダウンリストには255文字までしか入らないらしい。
おお!知らなかった。
でも、コロ子のコードでは「名前付き範囲を指定する」方法なので文字制限は気にしなくて良さそう。実際試してみたら255文字以上も大丈夫だった。
どういう状態なのかな。大丈夫かな?
状況が良く分からないけど、プルダウンで長い文字を選択するのはちょっと大変そう。
そこで、長~い文字を簡単に入力するにはどうすれば良いか考えてみた。
VLOOKUPのパワーアップ版のイメージでユーザーフォームを使うのはどうだろうか?
【例】
この表のコメント列を入力したい。
プルダウンで選択するには文字列が長すぎる。
一覧表シート
①長い文字列の選択用のシートを作成する(選択用コメントシート)
A列:見出し
B列:長い文字列(内容)
②ユーザーフォームを作成する
・表のD列でダブルクリックをするとユーザーフォームが立ち上がる。
・ユーザーフォームのリストに見出しが出る。
・見出しを選択すると、テキストボックスに内容が表示される。
テキストボックス内で内容の変更も可能。
・OKボタンを押すと表のコメント欄に内容が入る。
ではユーザーフォームを作ってみよう。
ユーザーフォーム名:UserForm2(いろいろいじっていたら2になっちゃった)
上のボックス:ListBox1
下のボックス:TextBox1
※TextBox1は改行可にするためにプロパティのEnterKeyBehavior をTrueにする。
OKボタン:CommandButtonで作成。オブジェクト名もCaptionも「OK」
コード
【Sheet1(一覧表シート)】
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'D列をダブルクリックした時ユーザーフォームを立ち上げる If Target.Column = 4 Then UserForm2.Show End If End Sub
【ユーザーフォーム】
'ユーザーフォームが開くタイミングで実行される Private Sub UserForm_Initialize() Dim i As Long Dim ws As Worksheet Set ws = Sheets("選択用コメント") For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row 'リストボックスに見出しを追加 ListBox1.AddItem ws.Cells(i, 1).Value Next i End Sub
'ListBox1(見出し)を選択した時 Private Sub ListBox1_Click() Dim i As Long i = ListBox1.ListIndex 'ListBox1のインデックス番号は0から始まる&2行目から始まる→+2 TextBox1.Value = Sheets("選択用コメント").Cells(i + 2, 2).Value End Sub
'OKボタンを押した時 Private Sub OK_Click() ActiveCell.Offset(0, -1).Value = TextBox1.Value 'ユーザーフォームを閉じる Unload UserForm2 End Sub
これじゃダメかな?