Excel VBA：エクセルのマクロで入力規則を設定する際のアラートの扱いがわからず困っています

質問

エクセルでINDIRECTを使った入力規則設定をマクロで行う際の、アラートの扱いがわからず困っており、お教えいただけると幸いです。

例えば、A列に日付、B列にその日付に行った都道府県名、C列にはB列で選択した都道府県に属する自治体名を入れる場合を考えます。

そのために、
　・北海道、青森、…という具合に都道府県名のリストに、あらかじめ『都道府県名』と言う名前をつけたものを作成しておく。
　・洞爺湖、阿寒湖、釧路、…という北海道の自治体名リストにあらかじめ『北海道』と言う名前をつけたものを作成しておく。
　・弘前、八戸、十和田、…という青森県の自治体名リストにあらかじめ『青森』と言う名前をつけたものを作成しておく。
　　：（以下同様）
と言う準備をしたあと
　-B列の入力規則ダイアログボックスで、入力値の種類を『リスト』、元の値として『=都道府県名』とすることでドロップダウンリストから都道府県名を選ぶことが出来ます。
　-C列の入力規則ダイアログボックスで、入力値の種類を『リスト』、元の値として『=INDIRECT(B1)』とすることでドロップダウンリストから自治体名を選ぶことが出来ます。
　　C列の規則を設定する際にB列にまだ都道府県名が入っていない場合は『元の値はエラーと判断されます。続けますか？』と言うアラートが出ますがOKを押して続行。
とすれば、C列のドロップダウンリストでB列で選択した都道府県に属する自治体名が選べることになります

ここまでは、教科書などにも書いてある内容なので問題はないのですが、これをマクロ化すると問題が発生します。

上記の入力規則の設定をマクロで記録し、実行すると
　『実行時エラー'1004'.アプリケーション定義またはオブジェクト定義のエラーです。』
が発生します。エラーの起こっている場所はC列の入力規則定義部分の
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:="=INDIRECT(B1)"
のようです。

B列に都道府県名が入った状態ではエラーとはならないので、マクロ記録時に『元の値はエラーと判断されます。続けますか？』が出てOKとした部分が
何らかの問題となっているとは思うのですが、対処方法がわからずに困っております。

ちなみに、On Error Resume Nextを入れておいたとしても、エラーでマクロが中断されることはなくなりますが、入力規則の設定は行われないので対処方法にはなりません。

質問が長くなり恐縮です。
コピーペーストなどで入力規則の設定が消えてしまうことがあるので、ブックの立ち上げ時に入力規則の再定義をしたいと考え、このような質問となりました。
対処方法おわかりの方、ご教授いただけると大変助かります。

なお、環境はwindowsXP、excel2003です。
よろしくお願いいたします。

回答No.2

end-u · ベストアンサー

>Formula1:="=INDIRECT(B1)"
ここでエラー制御してもいいかもしれませんね。
Formula1:="=IF(B1="""",B1,INDIRECT(B1))"

お礼

ご回答ありがとうございます。

Formulaの中にIF文を書くというのは、想像もしていなかった回答ですが、すごくエレガントに解決しそうです。

B1が空白だったときに、INDIRECT(B1)が設定されないのではないかと疑問に思って実際に試してみたのですが、全く問題なく動作するようです。
すごく勉強になりました。

この方法を採用してプログラミングを進めてみたいと思います。

本当にありがとうございました。

回答No.1

onlyrom

＞Ｂ列に都道府県名が入った状態ではエラーとはならないので

ということであれば、
Ｃ列の入力規則をセットする前に、Ｂ１にひとつ県名を入れおき
入力規則セット後、Ｂ１の県名を消去すればいいのでは？

'------------------------------------

　　　Ｂ列入力規則セット

　Range("B1").Value = "北海道"

　　　Ｃ列入力規則セット

　Range("B1").Value = ""

'---------------------------------

以上

お礼

ご回答ありがとうございます。

なるほど、こういう考え方もありましたか。
確かに、これだとばっちり解決しそうです。

B1に、最初から県名が入っていた場合は条件判断しないといけませんが、
色々と応用が利きそうです。

ありがとうございました。