[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『ドロップダウンリストの作成時、エクセルが正常に起動しなくなる件について』（アラン）
初投稿となります。VBA初心者です。質問文が少し長くなりますが、よろしくお願い致します。
現在、Windows10上のExcel2016を使用し、エクセルベースの社内システム（勤務表の自動作成ツール）を開発しています。 このツールを使用するのは、社内の一般ユーザで、エクセルに疎い社員に使用してもらう予定です。 このため、システムをガチガチに固めるような作りを考えています。
現在、職員データ一覧の画面を作成中です。 項目名などは調整中なのですが、以下の様なサンプルシートを作成中です。
|(職員番号)|(職員氏名)|(忌避職員リスト１)|(忌避職員リスト２)|(忌避職員リスト３)| |----------|----------|------------------|------------------|------------------| |1 | 職員１ | | | | |----------|----------|------------------|------------------|------------------| |2 | 職員２ | | | | |----------|----------|------------------|------------------|------------------| |3 | 職員３ | 4：職員４ | | | |----------|----------|------------------|------------------|------------------| |4 | 職員４ | 3：職員３ | 5：職員５ | 10:職員１０ | |----------|----------|------------------|------------------|------------------| |5 | 職員５ | 4：職員４ | | | |----------|----------|------------------|------------------|------------------| |6 | 職員６ | | | | |----------|----------|------------------|------------------|------------------| |7 | 職員７ | | | | |----------|----------|------------------|------------------|------------------| |8 | 職員８ | | | | |----------|----------|------------------|------------------|------------------| |9 | 職員９ | | | | |----------|----------|------------------|------------------|------------------| |10 | 職員１０ | 4：職員４ | | | --------------------------------------------------------------------------------
忌避職員リストは、相性の悪い職員が、同一の勤務に入らないようにするために設定する項目です。 忌避職員リストの項目は、全てドロップダウンリストです。 以下の条件で、職員番号もしくは、職員氏名が入力、更新された際に、忌避職員リストのドロップダウンリストを作成（更新）しようと考えています。
１．ドロップダウンリストの中身は「職員番号：職員氏名」とする。 ２．忌避職員リストは、自分自身を除いたドロップダウンリストとする（例えば、職員番号が「1」の行では、忌避職員リストで「1:職員１」は選択できない）。 ３．忌避職員リストは、互いに選択する運用とする（例えば、職員番号「3」と職員番号「5」の職員の相性が悪い場合は、職員番号「3」の行のリストで「5:職員５」を選択し、かつ、職員番号「5」の行のリストで「3:職員３」を選択する）。
以上の条件を反映させて、以下の様なサンプルコードを、Microsoft Excel ObjectsのSheet1に作成しました。
Option Explicit
Private Sub Worksheet_Change(ByVal target As Range)
Dim targetChangeTOF As Boolean
Dim syokuinNoList() As String
Dim syokuinShimeiList() As String
Dim syokuinNoShimeiList As String
Dim syokuinKojinShimeiRowSum As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
targetChangeTOF = False
'''''''''''''''''''''''''''''''''''''''''''
'忌避職員氏名のドロップダウンリスト更新処理
'''''''''''''''''''''''''''''''''''''''''''
'職員数を設定
syokuinKojinShimeiRowSum = 10 'サンプルとして10名を想定。将来的に変数化し、100名前後まで対応可能にする予定。
'忌避職員氏名のドロップダウンリストを初期化
syokuinNoShimeiList = ""
'職員番号もしくは、職員氏名の情報が変更されているか確認
If (target.Column = 1 Or target.Column = 2) Then
targetChangeTOF = True
End If
'職員番号もしくは、職員氏名の職員氏名が変更された場合は、忌避職員氏名のドロップダウンリストを更新
If (targetChangeTOF = True) Then
'職員番号と職員氏名の配列を作成
ReDim syokuinNoList(syokuinKojinShimeiRowSum)
ReDim syokuinShimeiList(syokuinKojinShimeiRowSum)
For i = 1 To syokuinKojinShimeiRowSum Step 1
'職員個人データの改行毎に、忌避職員氏名のドロップダウンリストを初期化
syokuinNoShimeiList = ""
For j = 1 To syokuinKojinShimeiRowSum Step 1
'忌避職員氏名のドロップダウンリスト一覧を作成（ただし同一行の職員氏名は、ドロップダウンリストの一覧に含まない）
If (j <> i) Then
syokuinNoList(j) = Cells(j, 1).Value
syokuinShimeiList(j) = Cells(j, 2).Value
syokuinNoShimeiList = syokuinNoShimeiList + syokuinNoList(j) + "：" + syokuinShimeiList(j) + ","
End If
Next
'忌避職員氏名のドロップダウンリストを作成
For k = 3 To 5 Step 1
Cells(i, k).Validation.Delete
Cells(i, k).Validation.Add Type:=xlValidateList, Formula1:=syokuinNoShimeiList
Next
Next
End If
End Sub
このコードは、職員数が10名の場合（syokuinKojinShimeiRowSum = 10の場合）は問題がないのですが、例えば職員数が「30名」になった場合（syokuinKojinShimeiRowSum = 30）、問題が発生します。 具体的には、一旦ファイルを保存した後に再度ファイルを開くと、以下のメッセージが表示され、[はい]を選択すると、ドロップダウンリストが作成されなくなるというものです。
「（ファイル名）の一部の内容に問題が見つかりました。可能な限り内容を回復しますか？ブックの発行元が信頼できる場合は、[はい]をクリックしてください。」
※なお、[いいえ]を選択すると、ファイルが開けません。
過去ログや関連情報を調べると、ドロップダウンリストを作成する以下のコードで、Formula1に代入する値の長さが255文字を超えると、ドロップダウンリストが正常に動作しないとの記載がありました。
該当コード Cells(i, k).Validation.Add Type:=xlValidateList, Formula1:=syokuinNoShimeiList
回避策として、ドロップダウンリストの中身をセルの範囲で指定する方法がありましたが、上記の条件１や２を満たすドロップダウンリストの作成方法が分かりません。
長文になり申し訳ございませんが、何か良案はございますでしょうか。 初めての投稿ですので、何かご不明な点がございましたら、その旨ご指摘頂けると幸いです。
以上、宜しくお願い申し上げます。
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
対策は、処理前にApplication.EnableEvents = False としておき、処理後に Trune に戻しましょう。
（???） 2017/04/02(日) 21:22
コードは追いかけていません。
私なら、SelectionCHangeで処理します。
まず "master" という名前のシートをつくり、社員一覧表を用意します。 運用時は非表示シートにしておいたほうがいいでしょう。 このシートの D列を作業列にしています。
|[A] |[B]
[1] |社員番号|氏名
[2] | 1|山田
[3] | 2|佐藤
[4] | 3|田中
[5] | 4|吉田
[6] | 5|鈴木
[7] | 6|山本
[8] | 7|斎藤
[9] | 8|加藤
[10]| 9|竹下
[11]| 10|若林
で、職員データ一覧シートのシートモジュールに
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim id As Variant
With Target(1)
If .Column > 2 And .EntireColumn.Cells(1).Value <> "" Then
id = .EntireRow.Cells(1).Value
If id <> "" And IsNumeric(id) Then 社員リストセット .Cells, id
End If
End With
End Sub
Private Sub 社員リストセット(Target As Range, exclude As Variant)
Dim v As Variant
Dim x As Long
Dim c As Range
Dim adr As String
With Sheets("master")
.Columns("D").ClearContents
With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
ReDim v(1 To .Rows.Count - 1, 1 To 1)
For Each c In .Cells
If c.Value <> exclude Then
x = x + 1
v(x, 1) = c.Value & ":" & c.Offset(, 1).Value
End If
Next
End With
With .Range("D1").Resize(UBound(v, 1))
.Value = v
adr = .Address(External:=True)
End With
End With
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & adr
End With
End Sub
★ただし、↑は、以下の点、手抜きです。
・いったん、ドロップダウンから 田中 を選んだとします。 そのあと、A,B列を田中に変更すると、本来は、ありえない 田中が残ったままになっています。 こういったケースを想定するなら、A.B列のChangeイベントで、C列から右のセルに 田中があればそれをクリアするということが必要ですね。 ・masterシートのメンテナンスで、ある名前が削除されたとして、職員データ一覧シートには残ったままになります。 ・忌避社員として同じ行に同じ名前が選択されても、チェックしていません。 ここはチェックしたほうがいいと思います。
（β） 2017/04/02(日) 21:33
???様、β様、ご回答ありがとうございます。明日以降、動作検証をしてみます。また質問させて頂くことがあると思いますので、どうぞ宜しくお願い申し上げます。 （アラン） 2017/04/02(日) 21:58
β様
今日、動作検証でシート「Sheet1」と「master」を作成し、サンプルコードを動かしてみたのですが、「Sheet1」に、うまくドロップダウンリストが表示されません。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim id As Variant
With Target(1)
If .Column > 2 And .EntireColumn.Cells(1).Value <> "" Then
id = .EntireRow.Cells(1).Value
If id <> "" And IsNumeric(id) Then 社員リストセット .Cells, id ←この行に辿り着けないようです。
End If
End With
End Sub
動作検証は、以下の方法で宜しいでしょうか。
１．マクロ有効のブックを作成する。 ２．シート「Sheet1」は空のまま「master」シートを作成し、社員データを入力する。 ３．シート「Sheet1」のシートモジュールに、サンプルコードをコピー＆ペーストする。 ４．シート「Sheet1」1行目の1列目に職員ID、2列目に職員氏名を入力する。 →この時点で「Sheet1」1行目の3列目〜5列目にドロップダウンリストが作成される。
初歩的な質問で申し訳ございません。 VBA初心者なので、作成して頂いたコードを調べながら解読している状態です。
以上、よろしくお願い申し上げます。
（アラン） 2017/04/06(木) 13:20
β様
お世話になります。あれから、ソースコードを色々と修正してみたのですが、思うような結果が得られませんでした。 もし宜しければ、お時間が宜しい時に、ソースコードが正常に動作するか、今一度検証をお願いしてもよろしいでしょうか。
その他の皆様
恐れ入ります、お時間がございましたら、β様のソースコードが正常に動作するか、検証をお願いしても宜しいでしょうか。 こちらの動作環境に問題があるかもしれません。
以上、よろしくお願い致します。 （アラン） 2017/04/18(火) 13:14
4/6 に追加質問いただいているのを見落としていました。
今から、質問を読んでみます。
（β） 2017/04/18(火) 13:16
まず、 Private Sub 社員リストセット(Target As Range, exclude As Variant) プロシジャも 同じシートモジュールに貼り付けてもらっていますね。（もし、貼り付けてなければ別のエラーになるはずですので 念のための確認）
>←この行に辿り着けないようです。
辿り着かない というのは、実際にはどのように確認されましたか？
いずれにしても、私が想定したのとはちょっと違っているように思います。
・まず、Sheet1 の １行目は タイトル行なんだろうなと思っています。
|(職員番号)|(職員氏名)|(忌避職員リスト１)|(忌避職員リスト２)|(忌避職員リスト３)|
・また２行目から下の A列、B列の番号や氏名は、あらかじめ入力されているんだろうなと思っています。 もちろん、その場で追加入力してもらってもかまいませんが、 ・で、処理としては C列含み、それより右側の列で、１行目にタイトルがあるセルが選択されたときに実行されます。 ・その時に「そのセルのみに」適切なドロップダウンリストを設定し、▼が表示されます。
たとえば D9 を選択したとします。-->A9 に番号が入っていれば、D9 に、その番号にふさわしいリストを設定します。 具体的な設定はサブプロシジャの 社員リストセット で行っていますが、いずれにしても、
>シート「Sheet1」1行目の1列目に職員ID、2列目に職員氏名を入力する。 >→この時点で「Sheet1」1行目の3列目〜5列目にドロップダウンリストが作成される。
こういうことではありません。
★同じ行でも C列に何か入っているとして、D列は C列にある名前は「忌避」するわけですから 同じ行の各セルに、同時に同じリストをセットすることはだめですよね。 （してもいいなら楽ですが）
（β） 2017/04/18(火) 14:03
たとえば master が
|[A] |[B]
[1] |社員番号|氏名
[2] | 1|山田
[3] | 2|佐藤
[4] | 3|田中
[5] | 4|吉田
[6] | 5|鈴木
[7] | 6|山本
[8] | 7|斎藤
[9] | 8|加藤
[10]| 9|竹下
[11]| 10|若林
こんな内容だとして、データシートのほうが
|[A] |[B] |[C] |[D] |[E]
[1] |(職員番号)|(職員氏名)|(忌避職員リスト１)|(忌避職員リスト２)|(忌避職員リスト３)
[2] | 1|山田 | | |
[3] | 2|佐藤 | | |
[4] | 3|田中 | | |
[5] | 4|吉田 | | |
[6] | 5|鈴木 |4:吉田 |2:佐藤 |
[7] | 6|山本 | | |
[8] | 7|斎藤 | | |
[9] | 8|加藤 | | |
[10]| 9|竹下 | | |
[11]| 10|若林 | | |
こんな内容だったとしたら、C2:E11 の中のいずれかのセルが選択されたら そのセルにのみ、リストをセットします。 たとえば E6 が選択されれば、E6 にのみ 鈴木、吉田、佐藤 を除く氏名のリストをセットします。
★元シートのほうですが、B列の名前は、コードでは参照していません。 あくまで A列の番号だけで処理しています。
（β） 2017/04/18(火) 14:11
β様
お忙しい中、ご丁寧な解説ありがとうございます。 こちらの動作検証の仕方が誤っており、大変失礼致しました。 明日、再度、動作検証をします。
以下、ご質問への回答です。
>まず、 Private Sub 社員リストセット(Target As Range, exclude As Variant) プロシジャも >同じシートモジュールに貼り付けてもらっていますね。（もし、貼り付けてなければ別のエラーになるはずですので 念のための確認）
はい。同じシートモジュールに貼り付けています。
>>←この行に辿り着けないようです。 >辿り着かない というのは、実際にはどのように確認されましたか？
該当行の前後にブレークポイントを設定して、動作を確認しました。 いずれにしろ、当方の動作検証の仕方が誤っていたため、明日再度確認致します。
>まず、Sheet1 の １行目は タイトル行なんだろうなと思っています。
データ行の1行目にタイトルを入れ忘れておりました。。ご指摘ありがとうございます。
>・また２行目から下の A列、B列の番号や氏名は、あらかじめ入力されているんだろうなと思っています。 > もちろん、その場で追加入力してもらってもかまいませんが、 >・で、処理としては C列含み、それより右側の列で、１行目にタイトルがあるセルが選択されたときに実行されます。 >・その時に「そのセルのみに」適切なドロップダウンリストを設定し、▼が表示されます。
ご丁寧な解説、分かりやすい具体例をありがとうございます。動作については、了解しました。 こちらに多々勘違いがあり、大変申し訳ございません。
>★同じ行でも C列に何か入っているとして、D列は C列にある名前は「忌避」するわけですから > 同じ行の各セルに、同時に同じリストをセットすることはだめですよね。 > （してもいいなら楽ですが）
本件については、結論から申し上げますと、同じ行の各セルに、同時に同じリストをセットしてもOKです。 理想としては、C列で選択されたデータをD列のリストから除外すべきですが… ソースコードのメンテナンス性を向上させるため、なるべく、分かりやすいコードで記述しようと考えています。
また明日、動作検証の結果をこちらでご報告致します。 重ね重ねになりますが、ご回答誠にありがとうございました。
（アラン） 2017/04/18(火) 22:37
コメント、訂正です。
>★同じ行でも C列に何か入っているとして、D列は C列にある名前は「忌避」するわけですから > 同じ行の各セルに、同時に同じリストをセットすることはだめですよね。 > （してもいいなら楽ですが）
と（偉そうに）コメントしましたが、コードを見直すと、それはやってませんでした。 やろうとして、それができる処理、A列のCHangeイベントではなく、C列以降の SelectionChange でやったんですが その手当てをしないままコードをアップしていました。
ですから
>結論から申し上げますと、同じ行の各セルに、同時に同じリストをセットしてもOKです。
セットのタイミングは セル選択時なんですが、同じ行には同じリストがセットされています。
（ β） 2017/04/19(水) 09:03
β様
ご連絡ありがとうございます。
>セットのタイミングは セル選択時なんですが、同じ行には同じリストがセットされています。
コメントの訂正ありがとうございます。了解致しました。この仕様で問題はございません。
先ほど、再度動作検証をした所、無事に該当のソースコードが動作しました。 本当にありがとうございます。
ただ、1点問題が… 本日確認したところ、表形式がA1方式ではなく、R1C1形式であることが分かりました。
R1C1形式にすると、頂いたソースコードの以下の3行に修正が発生すると思われますが、どのように修正すれば良いか分かりません。。
>.Columns("D").ClearContents
> With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
> With .Range("D1").Resize(UBound(v, 1))
初歩的な質問で本当に申し訳ございません。勉強不足を痛感しております。
ヒントや参考リンクだけでも構いません。お時間がかからない方法で、ご連絡を頂けると幸いです。
以上、よろしくお願い申し上げます。 （アラン） 2017/04/19(水) 17:23
OPtion で 列も記号ではなく 1,2,3,4,・・・・と表示されているんですね。
変更は adr = .Address(External:=True) これを
adr = .Address(External:=True, ReferenceStyle:=Application.ReferenceStyle)
こうするだけでいいはずです。 お試しください。 (こうしておけば、いずれの表示スタイルになっていてもカバーします）
（β） 2017/04/19(水) 18:15
β様
迅速なご回答、誠にありがとうございます。先ほど動作検証したところ、R1C1形式で無事に動作しました。 頂いたコードを活用させて頂きます。
大変勉強になりました。 本当にありがとうございました。
（アラン） 2017/04/19(水) 22:58
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.