こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

VBA/Worksheet_Changeがうまくいかない

エクセル2000です。
以下のワークシートチェンジイベントがうまくいきません。
Targetに値が入る場合は問題ないのですが、TargetをクリアしてもRange("F5").MergeAreaがクリアされません。
Targetをクリアした後、TargetをダブルクリックしてからEnterキーを押せばRange("F5").MergeAreaがクリアされるのですが、いちいちそうさせるわけにもいきません。
どうしたらよいのでしょうか?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$5" Then Exit Sub
If Target.Value <> "" Then
Range("F5").Value = Range("D42").Value
Else
Range("F5").MergeArea.ClearContents
End If
End Sub

投稿日時 - 2008-10-15 11:48:30

QNo.4403116

困ってます

質問者が選んだベストアンサー

こんにちは。

>   If Target.Address <> "$C$5" Then Exit Sub

結合セル(例:$C$5:$D$5)の値を消した場合、Target.Address は
$C$5:$D$5 になりますので、Exit Sub しちゃいそうですが...

結合セルであっても Change イベントはちゃんと発生します。

   If Target.Address <> "$C$5:$D$5" Then Exit Sub

としてみるとか。

また結合セルは複数のセルなのですから、Target.Value は、配列を
返します。したがって、単純に

   If Target.Value <> "" Then

と比較することはできません。

Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Intersect(Target, Range("C5")) Is Nothing Then Exit Sub
  
  Application.EnableEvents = False
  If Application.CountA(Target.Value) > 0 Then
    Range("F5").Value = Range("D42").Value
  Else
    Range("F5").MergeArea.ClearContents
  End If
  Application.EnableEvents = True

End Sub

ぱっと見なので、違ってたらスルー願います。

投稿日時 - 2008-10-15 16:03:03

お礼

KenKen_SPさま、いつもありがとうございます。
MsgBox Target.Address として試したところ、結合セルの場合、値を入力すると $C$5、Deleteだと $C$5:$D$5 と変わることが判明しました!
そのため、If Target.Address <> "$C$5:$D$5" Then Exit Sub では片方が作動しません。
また、Target.Value では、配列を返すとのことなので以下のようにしたらうまくいきました。
助かりました。ありがとうございます。

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C5")) Is Nothing Then Exit Sub
If Range("C5").Value <> "" Then
Range("F5").Value = Range("D42").Value
Else
Range("F5").MergeArea.ClearContents
End If
End Sub

投稿日時 - 2008-10-16 09:17:53

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(14)

ANo.14

こんにちは

値としての「長さ0の文字列※」が問題でしたか。
(※例えば、数式 ="" をセルごとコピーしてそのまま値貼付けした場合の""値)
 IsEmpty() とか、 .SpecialCells(xlCellTypeBlanks) とか、
VBAでの積極活用を考えると意外に面白いので、私は嫌いじゃないです。
これって、VBAでも数式でも、捉え方の問題って気もするし、オプションで非表示にした
ゼロ値と何処が違うの?って気もするけれど、嫌われてますよね。
数式音痴な私ですが、何か、使いこなす「セオリー」の様なものないのですかね。
避けて通る方が犠牲にするものが大きいような気もするのです。
本件では、.ClearContentsで何も犠牲にしていない筈ですから、いいのですけれど。

ところで、ずっと頭から離れなかったのが、
F5: =IF(C5="","",D42)  というシート上の数式でした。
もし私なら、この数式で困らない為の工夫を延々と考えて、
場合によってはシートの設計を変えちゃうかも知れませんね。
単に私の想像力が乏しいだけで、数式では不足があるのでしょう。
逸れた、過ぎた、話題ばかり、すみませんでした。

KenKen_SP さん^^、こちらこそ、ご面倒をおかけしました。
少しびびって(畏れ)コメント書いてましたが、あれでも本当は嬉しかったのですよ。
あらためて真意を書いてくださって、なお嬉しく、今後の勉強の励みになります。
ありがとうございます。
素敵な回答、きれいなソース、これからも楽しみにさせていただきます。

投稿日時 - 2008-10-17 06:45:52

お礼

おはようございます。
はい、まさしく文字長0の文字列が問題なんです。
エクセルなので、ワークシート関数で不整合が出ると困るのです。

でも本当の空白も =IF(C5="", でTRUEなのに、実際に "" を代入すると

=C5="" はもちろん TRUE
=ISBLANK(C5) では FALSE(空白でない)
=COUNTBLANK(C5)=1 では TRUE(空白だ)
=COUNTA(C5)=1 では TRUE(空白でない)
こんな矛盾がでてしまうので怖くて使えないんです。
変ですねえ。

F5を =IF(C5="","",D42) と数式にできないのは、D42(これはC5セルの値によりその都度計算でかわります)から転記されるのはあくまで暫定的な値で、このF5の値の変更を可能にするためです。
通常はD42で計算された値そのままでいいのですが、場合により変えることも可能にしておかなくてはならないのです。
数式ではどうやったって変更できませんよね。

なんどもありがとうございました。

投稿日時 - 2008-10-17 09:34:42

ANo.13

> 意外に test1の Value = "" の方が早いという結果がでました?!

(・∀・)へー...意外ですよね。

当方で、異なる2つの環境でテストしてみたら、4倍程度は test2 の
方が速かったですよ。理屈上でも VBA 標準コマンドである ClearContents
の方が自力実装の Null の値再セットより速い気がしますけども。

このようなケースもあるんですね。余談が過ぎました。

cj_mover 様へ

反意を以って回答したものではありません(むしろ逆です)から、ご心配
なく。心煩わせてしまい、申し訳ありません。

確かに、イベントプロシージャは稼働率が高いものですし、それが直接的に
ユーザー操作の快適性に関わるケースも多く、そのことを意識したソースを
書くことは非常に大切なのだと私も思います。

投稿日時 - 2008-10-16 23:36:24

お礼

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

投稿日時 - 2008-10-17 10:33:49

ANo.12

KenKen_SP さん、こんにちは
勉強になります。

私の書き方が悪かったでしょうか。
少なくとも、#11のキッカケを作ったのは私のようで(^^;)
なんら異論はありません。

.MergeArea.ClearContents
そのものに疑問がある訳ではなくて、
もし、= "" で代用が利くのだとしたら、
という話をしたかっただけなのですが。
計時にAPIを使うことも、
.ClearContents の件も、
複数のセルの値変更に対応させるように書かれたことも、
至極納得です。

思わぬ処で、興味ある記述に触れることが出来て
ありがたく存じます。

以後よろしくお願いします。

投稿日時 - 2008-10-16 16:28:09

お礼

今回はありがとうございました。
Value = "" にしなかった理由は#11のお礼に書いた通りです。
これからもご指導ください。

投稿日時 - 2008-10-16 18:20:59

ANo.11

話が膨らんでますね^^;  余談です。

値の消去は Null 値を代入し直すより、ClearContents した方が速い
(低負荷)のかもしれません。参考になりますか?

Private Declare Function timeGetTime Lib "winmm.dll" () As Long

' // 値の消去 Null値の代入 or ClearContents 簡易ベンチマークテスト

Private Const TEST_STR As String = "Sample String"

Sub test1()
  Dim t As Long
  Dim i As Long
  Range("A1:B1").Merge
  Application.ScreenUpdating = False
  t = timeGetTime()
  For i = 1 To 10000
    With Range("A1")
      .Value = TEST_STR
      .Value = ""
    End With
  Next i
  t = timeGetTime() - t
  Application.ScreenUpdating = True
  MsgBox Format$(t / 1000, "0.000") & "sec"
End Sub

Sub test2()
  Dim t As Long
  Dim i As Long
  Range("A1:B1").Merge
  Application.ScreenUpdating = False
  t = timeGetTime()
  For i = 1 To 10000
    With Range("A1")
      .Value = TEST_STR
      .MergeArea.ClearContents
    End With
  Next i
  t = timeGetTime() - t
  Application.ScreenUpdating = True
  MsgBox Format$(t / 1000, "0.000") & "sec"
End Sub

投稿日時 - 2008-10-16 15:54:11

お礼

KenKen_SPさま、ありがとうございます。
ベンチマークテストしてみたところ、意外に test1の Value = "" の方が早いという結果がでました?!

test1 1回目3.318 2回目3.296 3回目3.439
test2 1回目3.649 2回目3.686 3回目3.674

ただ、Value = "" を使うと、セル書式が文字列になっている場合、完全な空白ではなくなるようで、ISBLANKがFALSEなのにCOUNTBLANKでは空白に数えられるという不整合がおきます。それでClearContentsするようにしています。

投稿日時 - 2008-10-16 18:18:19

ANo.10

あ、すみません

"既定のプロパティ"
は、用語と誤解されそうなので、
一般語としての
「既存のプロパティ」
に読み替えてください。


念の為、
Target(1)を、#7のお礼欄にあるコードで使うとしたら、
無理があります。
Targetの中で最も左、最も上、にあるセルという意味なので、
例えば、A1:D5に貼付けを行うと、A1セルを参照してしまいます。
(セルの結合を適用した範囲に貼付けというのも限られた話ではありますが)
つまり、前稿は、
結合セルC5:D5のみの値変更に限定した内容になっています。

以上、訂正と補足です。
失礼しました。
それでは、また

投稿日時 - 2008-10-16 15:35:06

お礼

> 例えば、A1:D5に貼付けを行うと、A1セルを参照してしまいます。

If Intersect(Target, Range("C5")) Is Nothing Then Exit Sub にしたのでその通りでした。
If Range("C5").Value <> "" Then のままにしておきます。
わざわざありがとうございました。

投稿日時 - 2008-10-16 15:59:39

ANo.9

こんにちは

オマケ程度の話ですが、、、

元のコードのまま、
 Target

 Target.Cells(1, 1)
 Target.Cells(1)
 Target.Item(1, 1)
 Target.Item(1)
 Target(1, 1)
 Target(1)
のうちの何れかひとつと置き換えるだけでも、
元の狙い通りの結果になる筈です。
(私自身が最近教わった記述を含めて紹介しました)
(配列ではなく、コレクションのインデックスを指しています)

以上を元にして、手を加えるなら、
.Count > 2 と、.Row <> 5 と、.Column <> 3 で
範囲を判別するのがお奨めです。
(#7さんのように複数範囲の変更を捕える場合は別です。)
イベントプロシージャでは、なるべく
"既定のプロパティ"
(大雑把にいえばローカルウィンドウで確認できる類の純粋な意味でのプロパティ)
を優先して用いる方が好ましいかなぁと。
イベントプロシージャに関する回答では「学習者向け」の
内容を書くことも多いのですが、上に挙げたのは、
私自身実践で用いている方法です。

他、
 Range("F5").MergeArea.ClearContents
が必須なのか、
 Range("F5").Value = ""
で代用できるのか、、、
イベントプロシージャでの記述としては気になります。
または、仮に、非VBAで可能なことをイベントで処理するということなら
原則、私は消極的な考えです。
まぁ気になるという程度の話ですが、、、。

書きたいこと、伝えたいこと、他にも色々ありますが、
今回は要点(各論)だけのコメントにて、失礼させて頂きます。

投稿日時 - 2008-10-16 09:17:56

お礼

ありがとうございます。
If Range("C5").Value <> "" Then でうまくいきましたが、Target(1)の方がスマートですね。

いろいろありがとうございました。勉強になります。

投稿日時 - 2008-10-16 09:34:49

ANo.8

#7 です。すみません。

判別式を次のとおり訂正します。

  If Application.CountA(Target.Value) > 0 Then
     ↓
  If Application.CountA(Range("C5").MergeArea) > 0 Then

非常に限定されたセル範囲での話なので、前者でも大丈夫だと
思うのですが、判別セルを明確に絞り込んだソースの方がより
確実だと思いましたので。

投稿日時 - 2008-10-15 16:24:32

お礼

ご丁寧にありがとうございます。

投稿日時 - 2008-10-16 09:35:58

ANo.6

失礼しました。
以下のように変えてみてください。

Private Sub Worksheet_Change(ByVal Target As Range)
  If Len(Target.Address) > 4 And Target.Address <> "$C$5:$D$5" Then Exit Sub
  If Len(Target.Address) = 4 And Target.Address <> "$C$5" Then Exit Sub
  If Cells(5, "C") <> "" Or Cells(5, "D") <> "" Then
    Range("F5").Value = Range("D42").Value
  Else
    Range("F5").MergeArea.ClearContents
  End If
End Sub

標準モジュール
Sub test()
  Application.OnKey "{DEL}", "CellClear"
End Sub
Sub CellClear()
  If ActiveCell.Address = "$C$5" Then
    ActiveSheet.Range("C5").MergeArea.ClearContents
  End If
End Sub

投稿日時 - 2008-10-15 16:01:14

お礼

何度もありがとうございました。
勉強になります。

投稿日時 - 2008-10-16 09:04:45

ANo.5

統合セルには、Deleteキーの イベントが発生しないようで
コードはそのままにして、以下のマクロを追加してみてください。

Sub test()
  Application.OnKey "{DEL}", "CellClear"
End Sub
Sub CellClear()
  If ActiveSheet.Range("C5") Then
    ActiveSheet.Range("C5").MergeArea.ClearContents
  End If
End Sub

投稿日時 - 2008-10-15 15:37:50

ANo.4

Sub test()
  Application.OnKey "{DEL}", "CellClear"
End Sub
Sub CellClear()
  If ActiveSheet.Range("C5") Then
    ActiveSheet.Range("C5").MergeArea.ClearContents
  End If
End Sub

投稿日時 - 2008-10-15 15:31:36

ANo.3

No1です。
そうですね。失礼しました。
他のBOOKでは、問題なく動作するならば、正しく動作しないブックの問題かも知れませんね。
何かの設定がされているかも・・・
実際BOOKを見ないと分かりませんが・・・
最終的な方法としては、別のBookにするとか・・・

投稿日時 - 2008-10-15 14:19:12

お礼

やっとわかりました!!

TargetのC5セルがD5セルとMergeしているのが原因のようです。
結合を解除したところちゃんと作動しました。

ただ、レイアウト上、やはりD5と結合させたいのですが、Targetが結合セルの場合、コードをどう直せばいいのでしょうか?

投稿日時 - 2008-10-15 14:51:05

ANo.2

エクセル2000で試してみましたが、再現しませんね。

クリア後のチェンジイベントは、カーソルがそのセルから移動した時に発生しますが、そういう意味ではないですよね?
(ただし、デリートキーでセルをクリアした時は、その瞬間にイベントが発生するみたい)

デバッグ方法としては・・・
Range("F5").MergeArea.Select
Msgbox("Check")
Selection.ClearContents
などとしてみれば、どちらが効かないのかわかるかも。
あるいは、PC固有の問題の可能性もあるので、別のPCが近くにあれば、そちらで試してみるとか・・・

それでもダメなら、対処療法ですが・・・
上の行でF5に代入できているので、F5が結合の左上になっているのだと推測して、Range("F5").Value = ""と直接クリアしてしまえばなんとかなりませんか?
(根本解決ではないので、気持ち悪いけど)

投稿日時 - 2008-10-15 13:40:33

お礼

ありがとうございます。
No1のお礼にも書きましたが、特定のBOOKの問題のようです。

If Target.Value <> "" Then
Range("F5").Value = Range("D42").Value
MsgBox ("AAA")
Else
Range("F5").MergeArea.ClearContents
MsgBox ("BBB")
End If

とチェックしてみましたが、C5をDelキーでクリアしてもどちらのメッセージもでないのでイベントが発生していないのだと思います。(値が入力された場合はちゃんとAAAのメッセージが出ますが)

どんな原因が考えられますでしょうか?

投稿日時 - 2008-10-15 14:08:46

ANo.1

こんにちは。
もしかして、「Delete」キーを使えないように設定されていないですか?

  Application.OnKey "{DEL}", ""  '←「Delete」キーが使えない
  ↓
  Application.OnKey "{DEL}"    '←「Delete」キーを使えるようにする

投稿日時 - 2008-10-15 13:15:23

お礼

さっそくありがとうございます。
「Delete」キーでTargetのC5セルをクリアできてますので、「Delete」キーを使えないように設定されていないと思います。

今、あたらしいBOOKにまったく同じコードを入れて試したら「Delete」キーだけでもちゃんと作動しました。

どうやらできなかった特定のBOOKに問題があるようです。
どんな原因が考えられますでしょうか?

投稿日時 - 2008-10-15 13:54:53

あなたにオススメの質問