VBAにて連携するリスト登録する。

前提・実現したいこと

Excel VBAにて、連携するプルダウンリストを登録する。
その際、例として挙げた都道府県欄が空白でも登録できる様にしたい。

発生している問題・エラーメッセージ

Excelにて下記のシートを作成し、この項目にて、
例えば都道府県を選ぶと、それに紐づく区名が選択できる様にしたいと考えております。

この際に、行数がかなりあるため、マクロを使用し、自動的に入力規則をセルに対して設定できる様に試みておりますが、下記エラーが発生します。
手動で入力規則を設定する場合には、正常に登録できます。

追記：
プルダウン用リスト、一覧表を1つのExcelBookの別シートとして作成し、
ユーザはリストを修正できる様にします。
一覧表のユーザについては、図では4人ですが実際には1000人以上分の表となっており、
都道府県、区をプルダウンとして設定します。
一覧表については別途用意したボタンをクリックすると、DBからデータを取得し、ユーザ名を表示させる使用ですので、後からプルダウンを張り付けるのは手間がかかるという事で、データを作成する際にVBAを使用して追記する予定です。
都道府県については表示されるのですが、区は都道府県の内容と連動させるため、都道府県が空欄の場合にはエラーとなります。

ご指摘頂きましたので、内容を修正させて頂きます。

1004
アプリケーション定義またはオブジェクト定義のエラーです。

該当のソースコード

    Range("G7").Select

  With Sheet1.Range("G7").Validation
    .Delete
    .Add _
        Type:=xlValidateList, _
        Formula1:="=OFFSET(INDIRECT(F7),0,0,1,COUNTA(INDIRECT(F7)))"
  End With

試したこと

・都道府県欄が入力されている場合には、正常にリスト登録できる。
・都道府県欄が空欄の場合には、該当エラーが発生する。
・リスト登録時に空欄かどうかのチェックをし、エラーとしていると思われる。

Zuishin

2018/09/10 09:04

都道府県の数は限られているのであらかじめ全部書いておけばいいのでは？

Coore

2018/09/10 09:17

すみません。事例があった方が分かりやすいかと、都道府県を例に挙げました。リストを設定する方はユーザには渡さず、表への追加だけがユーザは済ませたいと考えております。

Zuishin

2018/09/10 09:21

どれがリストでどれが表かわかりませんが、ここでいう都道府県のデータをユーザーに渡さないのであれば、そもそも方法は無いと思います。

Coore

2018/09/10 09:43

ご指摘頂きましたので、情報を一部修正させて頂きました。ご指摘頂きありがとうございます。

行動規範の内容に同意します

回答1件

ベストアンサー

INDIRECT(F7)で、F7が空欄（東京でも大阪でもない）の場合、
INDIRECT(F7)は該当セルが見つからないですよね？

=OFFSET(不明セル,0,0,1,COUNTA(不明セル))
となるので、OFFSET関数はエラーになりますよね？

そのエラー範囲を入力規則に設定したら、
エラーになるというのが自然ではないでしょうか？

そして、数式は根本的に間違っていますので、
もう一度、勉強してみてください。

名前の定義を動的に拡張させる方法と、
プルダウンの参照先を動的に変える方法は、
別の考えですので。

それが分かれば、VBAを使う必要はありません。

投稿2018/09/11 03:06

ExcelVBAer

総合スコア1175

Coore

2018/09/12 00:16

ご回答いただき、ありがとうございます。やはりそうなりますよね。手動で登録する際に、「元の値はエラーとなります」というダイアログは出るものの、設定は可能なため、 VBAで設定した場合でもエラーを無視して登録する方法がないかと思い、質問させて頂いた次第です。ただ、VBAを使う必要があるプログラムですので、別な方法を検討してみます。ありがとうございます。