ＶＢＡのイベントについて・・・

質問

エクセルのＶＢＡで、エクセルのツールバーのデータの中の入力規則で、リストとゆうのに設定しました。

そして、私はエクセル２０００の開発環境で、リストから入力されたらマクロのチェンジイベントが発生するようなものを作りました。しっかり動きました。

しかし、それをエクセル９７の環境で動かしてみると全くイベントとして認識されないのです。

エクセル９７だからリストから入力するとゆうイベントが発生しないのか、他に違う理由があるのか誰か教えてくださ～い。

あと、エクセル９７と２０００以外で入力規則のリストからの入力をイベントとして認識できるかどうかを試せる方がいれば教えていただきたいです。
よろしくお願いします。

回答No.3

nishi6 · ベストアンサー

Worksheet_Changeイベントを使うことになると思いますが、Excel97では入力規則のリストによる入力ではイベントが発生しないはずです。何故？・・・分かりません。
Excel2000でできるようになったと思ったほうが合理的でしょうか。

ただ、Worksheet_Changeについては、セルの削除、挿入に対して満足できるイベントが発生しないので、恐くて仕事では使ったことはありません。使うのは、自分のみで使う時（OKWebで回答を書く時）くらいです。

(1)はWorksheet_Changeで変更を捉え、変更セルのValidation.Typeを調べています。

(2)はシートに入力規則範囲をCOUNTA関数で数えるようにしておき、
　　Worksheet_Calculate と Worksheet_SelectionChange で Worksheet_Change の代わりをさせて
　　いるつもりです。何か見苦しいですが。うまくいけば、Excel97で動くと思います。環境がないため
　　確認できていません。Excel2000では動きました。
　　これとは別ですが、Changeイベントが発生しない変更(シート名の変更など)をWorksheet_Calculate
　　で対応することは結構あります。

いずれもSheet1で実行しました。（Sheet1のコードウインドウに貼り付けます）
セルの挿入、削除は行わないものとしています。

(1)Worksheet_Changeを使用(Excel2000で確認)

Private Sub Worksheet_Change(ByVal Target As Range)
　　On Error GoTo ErrorHandler
　　If Target.Count = 1 Then
　　　　If Target.Text <> "" Then
　　　　　　If Target.Validation.Type = xlValidateList Then
　　　　　　　　MsgBox "リストから入力されました"
　　　　　　End If
　　　　End If
　　End If

　　Exit Sub
ErrorHandler:

End Sub

(2)Worksheet_Changeを使わない方法(Excel2000で確認)
入力規則がB3:B12とすると、シートのどこか(例えばA1)に =COUNTA(B3:B12) としておきます。

Dim selectAdr As String '選択したセル

Private Sub Worksheet_Calculate()
　　On Error GoTo ErrorHandler

　　'選択したセルに入力規則のリストが定義されているか
　　If Range(selectAdr).Text <> "" Then
　　　　If Range(selectAdr).Validation.Type = xlValidateList Then
　　　　　　MsgBox "リストから入力されました"
　　　　End If
　　End If

　　Exit Sub
ErrorHandler:

End Sub

'選択したセルのAddressを記憶
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　　selectAdr = Target.Address
End Sub

お礼

有難うございます。nishi6さんにも今回のシステムに関して大分お世話になりました。せっかく完成したと思ったのにこれではリリースできません。
いろいろ調べてみましたが、入力規則のリストがチェンジイベントとして認識されないのはどうやらエクセル９７の仕様らしいです。nishi6さんの言うようにWorksheet_SelectionChangeとの併用で実現できるとかなんとか書いてありましたが、どうやって併用するのでしょうかねぇ。（まぁこれはいいですが・・・）
今から解読していきますので、また遅れて上記に対して質問するかもしれませんので、そのときはどうぞよろしくお願いいたします。

回答No.6

todo36

>まずシート上にRange("A1")=COUNTA("入力規則のリストの範囲")を作りました。
COUNTAの引数は入力規制されたセルの範囲です。

ところで、さらなる改良案を思い付きました。

A1は"=myfunc(入力規制されたセルの範囲)"とする。

module1に次の関数を書く。

Public Function myfunc(Target As Range) As Long
MsgBox "リストから入力されました"
End Function

以上でリストを選択したときにmyfunc関数が呼ばれます。

私の場合は、リストで選択したときに合計欄をリフレッシュしたかったので
その処理をmyfunc内に書けばいい訳です。
かなりすっきりします。

お礼

Public Function myfunc(Target As Range) As Long
　　MsgBox "リストから入力されました"
End Function
これはなぜFunctionなのでしょうか？何か値を返してるのでしょうか。
ほんとに毎回理解できなくて申し訳ないです。
ところで、私も違う方法が思いつきました。
static変数を使って前の情報をもちます。それであとは、SelectionChangeとChangeを使ってどうにかしようと思っていますが、まだ考えはまとまっていません。頑張ります！！

回答No.5

nishi6

todo36さん、ありがとうございます。
動作確認できなかったので、少々、後ろめたさと不安がありました。ホッとしています。
かつ、すっきり変更してもらってありがとうございます。

多分、SelectionChange から考え初めて、タイミングを1つずらすために、1つ前のセル番地を記憶！として、
あとは Calculate をどうやって発生させるか考えたと思います。今思えば何か変ですね。

todo36さんの解答に書かれている『お礼』の疑問について、書かせてもらいます。

If ActiveCell.Text <> "" Then

　→これは、入力規則が設定されているセルに一旦入力した後、消去した時に
　　リストから入力されたイベントだと判定させないためです。消去もリストから入力されたもの
　　とするなら、この判定を外します。

If ActiveCell.Validation.Type = xlValidateList Then

　→これは、質問にある『入力規則で、リストとゆうのに設定しました。リストから入力されたら』
　　マクロのチェンジイベントが発生するようなもの　の『　』部分を表しています。
　　現セルの入力規則のタイプがリストだったら　の意味になります。

早くリリースできるといいですね。しかし、あせりは禁物です。では。

お礼

なるほどなるほど・・・
いろいろ考えまして教えていただいたものを組み合わせてみましたが、やはり９７では動きませんでした。どのようなまづいものを作ったかと言いますと、
まずシート上にRange("A1")=COUNTA("入力規則のリストの範囲")を作りました。
そしてイベントとして認識する場所をRange("A1")だけにします。
そして、あとは今までと同じWorksheet_Changeイベントで処理を行うというものです。

リストから選択されたという行為ではなくＡ１のセルのカウンタの数字が変わることによってイベントを発生させようと思ってやってみましたが・・・２０００では絶好調？でしたが、９７では無反応でした。

SelectionChangeだと変更しなくてもいきなり実行されてしまうので、ちょっとこわかったのです。nishi6さんのおっしゃる通りそのタイミングを考えるとうまくいくのだと思うのですが、私の知識では～o(゜д゜o≡o゜д゜)o
兎に角、もう少し頑張ってみます。無事リリース出来て、お盆にちゃんと実家に帰れるように祈りつつ・・・

回答No.4

todo36

nishi6さんありがとうございます。
(2)の方法でよさそうですね。

'少し改良(Excel97で確認)
Private Sub Worksheet_Calculate()
On Error GoTo ErrorHandler

'選択したセルに入力規則のリストが定義されているか
If ActiveCell.Text <> "" Then
If ActiveCell.Validation.Type = xlValidateList Then
MsgBox "リストから入力されました"
End If
End If
Exit Sub
ErrorHandler:
End Sub

お礼

う～ん、難しーーーぃ(>_<)
If ActiveCell.Text <> "" Then
これは何をしているのでしょうか？あ、意味はわかるのですが、空白じゃないかどうかを見る意味は何でしょうか？
If ActiveCell.Validation.Type = xlValidateList Then
これは何でしょうか？xlValidateList ←これはエクセルで設定したリストかどうかを見るものなのでしょうか？
あ～頭がくらくらしてきました～。
せっかく教えていただいてるのにいつも意味が理解できなくてすいません。ほんとに初心者なもので・・・

回答No.2

todo36

確かにExcel97でWorksheet_Changeイベントが発生しないですね。
私も解決策が知りたいです。
入力規則のリストって何？と言う方は↓参照

参考URL：

http://oshiete1.goo.ne.jp/kotaeru.php3?q=172487

お礼

有難うございます。
＞確かにExcel97でWorksheet_Changeイベントが発生しないですね。
やはり駄目でしたか・・・todo36さんに今までいろいろ質問してきて教えていただいたおかげでプログラムがせっかく仕上がったのに、開発環境が良すぎたみたいですね・・・大ショックです。

ちなみに、もう分かってるかもしれませんが、以前質問されていた入力規則のやり方ですが、
入力値の種類：リスト
元の値：=$Z$4:$Z$9
このようにされたいたみたいですが、$Z$4:$Z$9について名前定義そをすれば
リストの位置が変わっても変更する必要がなくなります。
この方が汎用性がありますし、他の解読性があるかと思います。
名前定義はツールバーの挿入→名前→定義です。
これを入力規則にするためには元の値を＝名前（自分で定義した範囲の名前です。
ここでは$Z$4:$Z$9につけた名前です。）

回答No.1

imogasi

私のＶＢＡ、ＶＢの理解技量が不足していて、理解できないのかもしれないので、恥を曝すかもしれないのですが
＞「ツールバーのデータの中の入力規則で、リストとゆうのに設定　した」－－＞ＶＢＡでは　
　Ｒａｎｇｅ（”Ａ１”）．Ｖａｌｉｄａｔｉｏｎ．Ａｄｄ　Ｔｙｐｅ：＝・・・、Ｆｏｏｒｍｕｌａ１
　：＝”＝＄Ｃ＄１：ＤＣ＄３”
　の事ではないですか。
　ここにはＣｈａｎｇｅイベントやイベントらしきものは出て来ず、（実際　　は内部的には、Ｉｔｅｍをクリックした時にイベントが起こっているの　　でしょうが、）セットしたセル（▼が現われるセル。上記ではＡ１セ　　ル）に値がセットされ　てしまう　のではないのでしょうか。すなわちセルと直結している。
＞「リストから入力されたらマクロのチェンジイベントが発生するような　ものを作りました」－－＞前段の理由から、どう言うものをお作りになったか想像できません。
●ＶＢでリストボックスをＵｓｅｒＦｏｒｍ１に貼りつけた時は、勿論諸イベントがあります。それを使い、その選択のきっかけを使って、テキストボックスやラベルなとに、選択されたＩｔｅｍをセットするのでしょうが。こちらにはＣｈａｎｇｅイベントはありましたっけ。Ｃｌｉｃｋなどが出てきますが私のＶＢでは出ない。
（エクセルのシートにもリストボックスは貼りつけられると思いますがそれは別として）
●エクセル９７でデータ－入力規則－設定－リストがないのでしょうか。 また「ツールバーのデータの中」は「メニューバーのデータの中」のことですか。
エクセルバージョン５．０の頃は、ＯｎＡｃｔｉｏｎイベントで、イベントを捉えていたように思いますが、９７ならそんなに旧いものではないし。

お礼

う”～ん・・・なんだか難しくてよくわからなかったです。せっかくアドバイスしていただいたのにすいません。
エクセル９７に入力規則でリストがあるかどうかはわかりませんが、リストから選択するという行為はＶＢＡのチェンジイベントとして認識されないのは、エクセル９７の仕様のようです。
有難うございました。