VBAでセルに入力規則（選択し）を設定する

VBAでセルに入力規則（選択し）を設定する

ＥＸＣＥＬのシート上のボタンに下のようなコードを書いて実行すると
「アプリケーション定義又はオブジェクト定義のエラー」が矢印のところで出てしまいます。
独学でチャレンジしていますが、原因がわかりません。どなたか原因と対策を教えて下さい。
　　
　//ボタンの中のコード
　//セル、$E$147:$E$158　にセットした「選択し」をセル E7 へセットする。
　Dim CRng As Range
　Set CRng = Range("E7")
　With CRng.Validation
　　.Delete
　.Add Type:=xlValidateList, Formula1:="=$E$147:$E$158"　　←ここでエラー
End With

No.4 ベストアンサー 回答者： myRange 回答日時： 2010/06/25 11:37

またまた、myRangeです。

原因がわかりました。
試したところエクセルのバージョンによって上手くいったりいかなかったりするようです。

xl２０００は、質問者の言うとおりエラーが出る
xl２０１０は、上手く動作する

（xl２００３、２００７は使える環境にないので試していません）

但し、CommandButtonを使わないで直接マクロを実行するとどちらでも動作します。


で、解決策として、コードの最初に、

　　Range("E7").Select

を入れてセルＥ７をアクティブにしてやるとバージョンに関係なく動作するようです。

以上です。

この回答へのお礼

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

Range("E7").Select　を追加しましたら、うまく動作するようになりました。
どうもありがとうございました。

お礼日時：2010/06/25 12:50

No.3 回答者： myRange 回答日時： 2010/06/24 23:07

回答２、myRangeです。

あらら、シートは保護してなかったですか。。


では、提示したコードだけではなくて、
質問者のシート構成、コードの場所など以下のことを補足してください。
さすれば、すぐ解決するでしょう。。。(^^;;;

●入力規則をセットするセルＥ７のシート名
●リストのＥ１４７～Ｅ１５８のシート名（Ｅ７と同じでしょうが念のため）
●ボタンの配置してあるシート名
●ボタンの種類はフォームのボタンなのか、コントロールツールボックスのボタンなのか
●コードの書いてある場所（標準モジュールか、ボタンのあるシートモジュールか）
●コードを端折らずに、Sub xxxx_Click()～～End Subまで全て提示

▲データの内容は不要です。

以上です。

この回答へのお礼

補足事項に関しては以下の通りです。
●入力規則をセットするセルＥ７のシート名 ⇒sheet1
●リストのＥ１４７～Ｅ１５８のシート名（Ｅ７と同じでしょうが念のため）⇒sheet1
●ボタンの配置してあるシート名 ⇒sheet1
●ボタンの種類はフォームのボタンなのか、コントロールツールボックスのボタンなのか コントロールツーボックスのボタンです。
●コードの書いてある場所（標準モジュールか、ボタンのあるシートモジュールか）⇒ボタンの中です。
●コードを端折らずに、Sub xxxx_Click()～～End Subまで全て提示
　　以下のとおりです。
Private Sub CommandButton1_Click()

ActiveSheet.Unprotect

Dim CRng As Range
Set CRng = Range("E7")
With CRng.Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=$E$147:$E$158"
End With

MsgBox "選択肢の更新が完了しました。", vbOKOnly + vbInformation

End Sub

念のため、新しいＢｏｏｋを作り、sheet1にボタンを追加してコードを記述してみました。
当然、sheet1のボタン以外には何もコードが記述されていない状態です。参照もしていません。
しかし、結果は同じようにエラーです。

コードそのもがおかしいのでは？と感じていますが。

お礼日時：2010/06/25 09:00

No.2 回答者： myRange 回答日時： 2010/06/24 21:24

　

コード的には問題はないので、考えられるとしたら。。。

シートの保護、をしてませんか？

であれば、
コードの最初で
　　Activesheet.UnProtect
コードの最後で、
　　Activesheet.Protect
としてみるといいでしょう。

●尚、Protect,UnProtectの引数（PassWordなどは省略してます）
引数は、シート保護のマクロ記録を取ってみるとわかります。
　
以上です。

　

この回答へのお礼

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

シートの保護はしていません。
念のため、Activesheet.UnProtect　を追加しましたが、結果は変わらずエラーとなりました。

データがおかしいのかと思い、選択肢に文字を再セットしましたが、結果はエラーでした。

お礼日時：2010/06/24 22:02

No.1 回答者： greyseal 回答日時： 2010/06/24 18:53

エラーが出たという場所のセルの住所を「Ｅ１４７からＥ１５８まで」を「Ｅ１０からＥ１５まで」に変えただけですが、ちゃんとできましたよ？

Sub test()

Dim CRng As Range
Set CRng = Range("E7")
With CRng.Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=$E$10:$E$15" '←１４７を１０に、１５８を１５に。
End With

End Sub

この回答へのお礼

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

ご回答のとおり私もやってみましたが、同じエラーが出てしまいました。
ボタンを新しく作成し、やってみましたがやはり、エラーです。
何が違うのでしょうか。

私のＰＣは
　　ＯＳ：ＸＰ
　　エクセル：Ｅｘｃｅｌ２０００
　　ＶＢ：Visual Basic 6.0
です。

お礼日時：2010/06/24 20:19