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.5

kkkkkm

> (4) マウスカーソルをC1の状態で「プロパティ」を選択し、以下を入力する。

開発タブでコントロールのところでデザインモードにし、ComboBoxを選択して右クリックで出たメニューのプロパティを開くと前回の画像のプロパティがでます。
そこで設定します。
ComboBoxでプルダウン利用して選択するとC1と連動します。連動が確認出来たらC1の上にComboBoxを重ねるとC1でリストを開いているように見えますし、データもC1に入っているので他からC1を参照しても選択した値で計算されます。

また、蛇足ですが、BoundColumnを「2」にすると、C1の値はB列で設定している値(東京都など)が表示されます。
C1に重ねているので見た目はC1にA列で設定した値が表示されているように見えます。
たとえば、ComboBoxで「A」を選択した場合、どこかのセルに=C1としたらそこには「東京都」と表示されます。
添付画像はE1に=C1としています。

ただ、C1に「A」があると思って参照した場合は、違った結果になるので「2」にするのは、特殊な使い方かもしれません。

回答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でコードに追加記述してエラーをなくする（＝プルダウン選択だけではなく、直接入力も可とする）事は実現可能なのでしょうか？

回答No.3

imogasi

小生が前の質問で示唆したように、関数では難しいと思う。そもそも、希望内容が珍奇なのだ。
ーー
VBAの回答でやろうとしたら、すでに＃２に似た回答があった。数字部分が、複数桁ついでもOKになるように
Sheet1など、当該シートの、シートモジュールに、Changeイベントを設定して、
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then　’A列なら
s = ""
For i = 1 To Len(Target)
If IsNumeric(Mid(Target, i, 1)) Then
s = s & Mid(Target, i, 1)
End If
Next i
Target = s
End If
End Sub
ーー
＃２に比べ、左から数字で、続いている数字だけ返す。
数字＋文字列混在の文言から、数字だけの抜出しが、よい関数がなく、面倒。
普通は、正規表現など使うのかも。
数字＋文字＋数字だと予想外の結果になるのかな、そういう場合のデータはないのかな。
ドロップダウンのリストの文字列で、左から数字が連なっている（１－N桁）状態が、上記VBAで意図した内容。
質問で、データ状況の説明がないから紛らわしい。
例
ドロップダウン（A列設定）のリストの文字列について、
1 大阪　　ーー＞選択するとA列に１を返す
2 京都　　ーー＞２を返す
3 奈良　　ーー＞３返す
もし、リストの文言が　１１　大阪なら１１をA列同行に返す。
数字部分は全角でもこのままで、OKかと思う。

お礼

VBAコードの拡張版をご提示頂いて感謝いたします。
ありがとうございました。

「希望内容が珍奇」という件に関し、補足説明します。
当方の所属している同好会では代々 Excel を使った入力フォームを使っており、Excel には項目そのものを入れず A～Z の文字や数字を入れるようになっています。（例：東京都ならば A）　今回はプルダウン方式で入力しやすくするのが目的です。

回答No.2

kkkkkm

前回のActiveXコントロールのComboBoxを利用する方法でやってみてはいかがですか。

補足

こちらにも再び顔を出して頂いてありがとうございます。

ご提案頂いた内容でチャレンジしているのですが、私が良く理解できていないためか、前回同様に同じところでハマっています。
何が間違っているのか、ご教示頂ければ幸いです。

＜実行した内容＞
(1) A1からA4に　A B C D を入力
(2) B1からB4に　東京都　大阪府　北海道　沖縄県　を入力
(3) C1 の位置に「開発」－「挿入」－「ActiveXコントロールのComboBox」を選択し配置する。
(4) マウスカーソルをC1の状態で「プロパティ」を選択し、以下を入力する。
　　　ColumnCount 2
CoumunWidths 15pt,15pt
Linked Cell C1
　　　ListFillRange A1:B4
(5) マウスカーソルをC1の状態で、「データ」－「データの入力規制」をやっても「データの入力規制(v)」がグレイアウトし選択できず、プルダウンリストが作れない。

回答No.1

SI299792

原因ですが、文字列の書式設定はプルダウンにに反映されない。仕様だからしょうがないです。

対策。A1～A4にプルダウンがあるとします。

①あきらめて別セルでA にする。
B1: =LEFT(A1,1)
下へコピペ。

②VBA
'
Private Sub Worksheet_Change(ByVal Target As Range)
'
If Not Intersect(Target, [A1:A4]) Is Nothing And Target.Count = 1 Then
Application.EnableEvents = False
Target = Left(Target, 1)
Application.EnableEvents = True
End If
End Sub
シートモジュールに入れて下さい。シート名の所を右クリック、コードの表示を選び、そこに入れて下さい。

１文字が前提です。２文字以上ある場合、補足に書いて下さい。

補足

早速のコメントをありがとうございました。
以下のようにすると、うまくいきました。

(1)A列のセルに、ご教示頂いた、VBAのコードを貼り付ける。
(2)ご回答頂いた画面を参考にG列に元データーを入れる。
　　（G1 に 「A 東京都」、G2 に「B 大阪府　………）
(3)A列に、「データ」－「データの入力規制」を選択し、元の値として、G1:G4 の範囲を選択する。

このようにして、一応やりたい事は実現できたのですが、ご回答にある、「あきらめて別セルでA にする。B1:=LEFT(A1,1)　下へコピペ。」はやっていません。
B列のこの処理は何のためにあるのか？　ご教示頂ければ幸いです。　以上、よろしくお願いいたします。