プロが教えるわが家の防犯対策術!

エクセル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

A 回答 (14件中1~10件)

こんにちは。



>   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

ぱっと見なので、違ってたらスルー願います。
    • good
    • 0
この回答へのお礼

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

こんにちは



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

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

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

おはようございます。
はい、まさしく文字長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

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



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

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

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

cj_mover 様へ

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

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

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

お礼日時:2008/10/17 10:33

KenKen_SP さん、こんにちは


勉強になります。

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

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

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

以後よろしくお願いします。
    • good
    • 0
この回答へのお礼

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

お礼日時:2008/10/16 18:20

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



値の消去は 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
    • good
    • 0
この回答へのお礼

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

あ、すみません



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


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

以上、訂正と補足です。
失礼しました。
それでは、また
    • good
    • 0
この回答へのお礼

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

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

お礼日時:2008/10/16 15:59

こんにちは



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

元のコードのまま、
 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で可能なことをイベントで処理するということなら
原則、私は消極的な考えです。
まぁ気になるという程度の話ですが、、、。

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

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

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

お礼日時:2008/10/16 09:34

#7 です。

すみません。

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

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

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

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

お礼日時:2008/10/16 09:35

失礼しました。


以下のように変えてみてください。

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
    • good
    • 0
この回答へのお礼

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

お礼日時:2008/10/16 09:04

統合セルには、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
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています