Excel プルダウン 補足説明付きの場合 その２

質問

（実現したい事１）　←　既に解決済

例えば、Excel プルダウンで、↓ にマウスを当てると以下のように表示され、

　1　確認完了
　2　確認中
　3　未着手
　4 　非該当

選択後は、1から4の数字だけがセルに格納され、補足説明部の　確認完了、確認中、未着手、非該当　などはセルに格納されないようにしたい。

（ご回答頂いた解決方法）

G1に1、G2に2、‥‥のように数字だけを入力します。
↓
セルG1の書式設定で、表示形式を「G/標準 確認完了」のようにユーザー設定します。
セルG2以降も同様に、"G/標準＋スペース＋表示したい文字列" の様式で表示形式を設定します。
↓
G列が "1 確認完了"、"2 確認中"、‥‥ のように表示されます。
↓
この範囲を、リスト(入力規則)の元の値として指定します。

（実現したい事２）　←　今回、困っている事。

例えば、Excel プルダウンで、↓ にマウスを当てると以下のように表示され、

　A 東京都
　B 大阪府
　C 北海道
　D 沖縄県

選択後は、AからDの文字だけがセルに格納され、補足説明部の　東京都、大阪府、北海道、沖縄県　などはセルに格納されないようにしたい。（前回は数字であったが今回は文字）

（チャレンジした内容）

数字の場合と同様に、

G1にA、G2にB、‥‥のように文字だけを入力します。
↓
セルG1の書式設定で、表示形式を「@ 東京都」のようにユーザー設定します。
セルG2以降も同様に、"@＋スペース＋表示したい文字列" の様式で表示形式を設定します。
↓
G列が "1 東京都"、"2 大阪府"、‥‥ のように表示されます。
↓
この範囲を、リスト(入力規則)の元の値として指定します。

このようにやってみたのですが、プルダウンのリスト表示では、

　A 東京都
　B 大阪府
　C 北海道
　D 沖縄県

とはならずに、
　A
　B
　C
　D

のような補足説明が付加されないようなリストとなってしまいます。

一方で、例えば G1 は「A 東京都」と表示され、G1を修正しようとして G1 セルをダブルクリックすると、「A」のみが表示されるので
「セルの書式設定」－「ユーザー定義」は正しく設定されています。

なぜ、プルダウンのリスト表示だけ補足説明部が付加されないのか、理由がわからず悩んでいます。
この原因と解決案をアドバイスを頂けたら幸いです。

なお、環境は Windows 10　＋　Office2016　です。

以上、よろしくお願いいたします。

回答No.4

misatoanna

適用できるのは数字だけなのは仕様ですので、文字列を使用する場合はマクロに頼るほかないでしょう。

A1セルに入力規則(リスト)を設定し、「A 北海道、B 青森県、C 秋田県、D 山形県、‥‥」と入力されているセル範囲をリストの[元の値]にします。
↓
シートタブの右クリックメニューにある"コードの表示"から開くウィンドウに以下を記述し、そのウィンドウを閉じます。
-----
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) <> "A1" Then Exit Sub
Application.EnableEvents = False
Target.Value = Left(Target.Value, 1)
Application.EnableEvents = True
End Sub
-----

ここまで設定したら、A1のリストを操作してみてください。

補足

コメントありがとうございます。

回答No.1 で SI299792 様から今回とほぼ同じようなVBAコードをご提示頂き、動作確認できました。

ついでの質問で大変恐縮なのですが、VBAコードをセルに埋め込む今回の方式では、プルダウンでの選択ではなくて直接セルに（例えば A と）書き込んだ場合は入力規制エラーが出てしまいますが、VBAでコードに追加記述してエラーをなくする（＝プルダウン選択だけではなく、直接入力も可とする）事は実現可能なのでしょうか？