プロが教える店舗&オフィスのセキュリティ対策術

始めまして

Private Sub Worksheet_Change(ByVal Target As Range)が全然整理できないので
アドバイスいただけますか
私の能力は
1、N88 BASIC を少しかじりました
2、エクセルで式の入力は少しできます(ロータス123も少しやりました)
3、昔、クイックベーシックはギブアップしました

最近ずっと、知恵袋やここのgooのエクセルの掲示板から、やりたいことを
可能にしてくれるマクロを探しているのですが、いろいろな表現があって、全然整理できません
例えば下記のマクロなどです(行には自分でメモを書いています)

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  '複数個所同時入力を拒否する?  
  If ActiveSheet.AutoFilterMode Then Exit Sub
  'AutoFilterModeだったら拒否する
  If Not Intersect(Target, Range("A1:D4")) Is Nothing Then
  MsgBox Target.Address '実際の処理
  End If
End Sub

さらには
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A1")) Is Nothing Then
  'A1の値が変わった
    MsgBox "セル A1 が変更になりました。" ' <--- A1変更時の処理
  End If
  If Not Intersect(Target, Range("B1")) Is Nothing Then
    MsgBox "セル B1 が変更になりました。" ' <--- B1変更時の処理
  End If
  If Not Intersect(Target, Range("C1")) Is Nothing Then
    MsgBox "セル C1 が変更になりました。" ' <--- C1変更時の処理
  End If
End Sub

など・・・
他にも
Dim myRng As Range
Dim r As Range
を使いなさいとか

さらには、

Application.EnableEvents = False
Application.EnableEvents = TRUE
を使いなさい

そのマクロ自体が、別のセルを代入したりすると、もうひとつのイベントを発生させてしまい、プロシージャの中で、無限ループに近い形になっています。(ただし、完全な無限ループではないので、1万回程度で止まります。)そこで、その無限ループをとめなくてはならないので、そうした、
Application.EnableEvents = False
ということをします。

などです

===================================================

そこで、さっぱり整理できないので、自分がやりたいことはマクロでどう書けば良いのかを
教えていただければと思います
教えていただいてから、ひとつ、ひとつ検索して勉強します

下記がマクロでやりたいことです

===================================================
入力を監視するセルは下記の範囲で、範囲ごとにやること(処理)は違います
入力は全て整数です(整数が入力されたらその値に応じてマクロで処理します)
入力値のチェックは「入力規則」でやります(IF文書くのがたいへんそうなので)

入力範囲は

Range("B2,D2,H2,J2")
Range("B3,D3,H3,J3")
Range("B5:B100") 
この範囲に値の貼り付けで入力は禁止です、というか、禁止しないと
だめなようです(Worksheet_Changeは)

Range("H5:H100")
この範囲に値の貼り付けで入力は禁止です、というか、禁止しないと
だめなようです(Worksheet_Changeは)

もっと増えるかもしれませんが、その時はなんとか教えていただいた例を参考にやってみます
いきなりの長文の質問ですみません

用語に不慣れで表現が分かりにくかったら指摘してください
よろしくお願いします
エクセルはバージョン2013を使っています
最後まで読んでいただきましてありがとうございました

質問者からの補足コメント

  • 'If Target.Count > 1 Then Exit Sub

    報告いたします
    上のようにレム文にしたら、複数行の貼り付けでエラーになりました
    If Target.Count > 1 Then Exit Subが効いていることを確認できました
    ありがとうございました。エラー処理が実感できると身につきますね

    Application.EnableEvents = False
     マクロで書き込みがある場合は、これで挟む
    Application.EnableEvents = True
    上の2行もエラー処理のひとつかと思いますが
    まだエラーを実体験できません

    マクロは奥が深くて大変ですね
    大ぼら吹いてしまいました
    請け負ったのは失敗でした
    Worksheet_Changeは奥が深いです

    No.1の回答に寄せられた補足コメントです。 補足日時:2017/09/21 18:54
  • ありがとうございました
    Worksheet_Change の理解度がアップしました
    質問を締め切らせていただきます

    No.3の回答に寄せられた補足コメントです。 補足日時:2017/09/22 15:16

A 回答 (3件)

こんにちは。



>Application.EnableEvents = False の効果を知るには、無限ループになるようなコードが
書けないとだめということですね
>「そんなコードが書けないのだから、効果を実感できるわけがない」という話ですね
>やっと合点しました

私は、そこまで言うつもりはありません。最初は形式的に書いていて、そのうちに分かるようになるということです。実際に、今、「そんなコード」を本当に書ける人がいるのでしょうか?

らしきコードを書いてみましたが、これでも、実証はできません。途中でエラーがでてしまいます。止める場合は、ESCキーを押します。

'A2に値をいれるマクロ
Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Count > 1 Then Exit Sub
 If Target.Value = "" Then Exit Sub
 If Target.Address(0, 0) <> "A2" Then Exit Sub
'以上除外設定
Application.EnableEvents = False
Target.Value = Target.Value + 1  '値に値を入れるから保護する必要があります。
Application.EnableEvents = True
If Target.Value > 10 Then Exit Sub  '再帰になって場合の気休め。実際は働かない
 Exit Sub
End Sub

最近は、PCそのものの性能がよいせいか、忘れてしまう人がいます。無限ループにならないのは、一回のイベントにわずかなタイムラグが出来て、そのタイムラグが、イベントをキャッチする時間の範囲から外れてしまうから、イベントが終了するわけです。

以下の場合は、EnableEvents =False は、不要です。
マクロで値を入れた時に、再イベントが発生するのです。だから、それを防ぐわけです。
-----------------
 If Not Intersect(Target, Range("B2,D2")) Is Nothing Then
MsgBox Target.Value
MsgBox Target.Address
MsgBox Target.Column
MsgBox Target.Row
End If

If Not Intersect(Target, Range("B5:B10,D5:D10")) Is Nothing Then
MsgBox Target.Value
MsgBox Target.Address
MsgBox Target.Column
MsgBox Target.Row
End If
-----------------

>マクロがエラーで止まって、エラーの行が黄色くなって
>コードが示されるようなことでは、逆にマクロなんか使わない方がよさそうです

問題は、そういう所ではないのです。
最初は、プログラムそのものを体得していくことだと思います。
上達のポイントは、なんとか、何年掛かってもよいからと思いつつ、完成を目指すことです。

その都度、覚えた技術を反映しながら、自分のコレクションに加えていきます。

また、ネットで勉強するにしても、なるべく一定の人のものを追いかけて、その人がどう考えて、そのコードになったのか考えながらでもよいと思います。ある程度の内容になると、その人の人柄がコードに出てきます。

マクロで、自分がやりたいことができるようになるには、なかなか時間が掛かります。でも、それ以上に、相手の期待に沿うというのは、もっと大変なことです。

最近、質問した人は、私の説明を読んで、すぐに諦めてしまって質問を閉じてしまいました。こちらも事情があって、その人のために、大掛かりなプログラムを簡単に公開したくはありません。人にやる気を起こさせるのも技術かもしれません。中には、長いコードをみただけで、自分の質問とは違います、と断ってしまう人。せっかく作っても、予定に間に合わなかったので、ボツ。エラー処理したコードは、内容が複雑になっているから、それはダメ。

他人のためにマクロを書くと考えていたら、とてもやっていられるものではありません。
この回答への補足あり
    • good
    • 1
この回答へのお礼

何度もフォローいただきありがたいことです
私は、WindFallerさんを怒らせてしまったかと心配しました

サンプルマクロ書いていただきましたのでよくわかりました
「マクロでセルに書き込みする時は前後を挟む」のですね
合点しました

Application.EnableEvents = False
Target.Value = Target.Value + 1  '値に値を入れるから保護する必要があります。
Application.EnableEvents = True

お礼日時:2017/09/22 12:14

#1の回答者です。



>>Application.EnableEvents = False
>> マクロで書き込みがある場合は、これで挟む
>>Application.EnableEvents = True
>上の2行もエラー処理のひとつかと思いますが
>まだエラーを実体験できません

それは、ご自身がおっしゃっていた話ではありませんか?
>もうひとつのイベントを発生させてしまい、プロシージャの中で、無限ループに近い形になっています。

つまり、再帰(recursive)してしまうので、それを防ぐようにしてもよいのですが、それだけのことを書くのなら、EnableEvens の方が楽だと思います。

なお、Excel 2013 以降は、ヘルプがほとんど使い物になりません。たぶん、ユーザーの追い出しだと思います。この先に、記録マクロがなくなるはずです。また、メソッドのオブジェクト化が進んでいくと思います。例えば、すでにソートメソッドは、オブジェクト化しています。
その辺りの対策を取っておかないといけないようです。やがてヘルプが有償で売られるようになるかもしれません。英語版はすでに有償のヘルプがあります。
    • good
    • 0
この回答へのお礼

分かりました
Application.EnableEvents = False の効果を知るには、無限ループになるようなコードが
書けないとだめということですね
「そんなコードが書けないのだから、効果を実感できるわけがない」という話ですね
やっと合点しました
何度もフォローしてもらってありがたく思います
テストのために2つに分けてみました
この場合 Application.EnableEvents = False と Application.EnableEvents = True
はどこに書いたらよいでしょうか

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
'複数個所同時入力(複数セルの値の貼り付け)を拒否する
If ActiveSheet.AutoFilterMode Then Exit Sub
'AutoFilterModeだったら拒否する
'エラー処理は入力規則で済み

 If Not Intersect(Target, Range("B2,D2")) Is Nothing Then
MsgBox Target.Value
MsgBox Target.Address
MsgBox Target.Column
MsgBox Target.Row
End If

If Not Intersect(Target, Range("B5:B10,D5:D10")) Is Nothing Then
MsgBox Target.Value
MsgBox Target.Address
MsgBox Target.Column
MsgBox Target.Row
End If

End Sub
余談ですが、入力と印刷程度の知識しかない方に使ってもらうには
マクロがエラーで止まって、エラーの行が黄色くなって
コードが示されるようなことでは、逆にマクロなんか使わない方がよさそうです
エクセルは、式だけ書いているのが楽そうです
ちょっとやる気が失せてきました

お礼日時:2017/09/22 00:44

ご質問者さんは、もう十二分に心得があるようで、いわゆる帰納的学習(経験を積むこと)で上達する以外はありえませんね。


ただ、ご質問がよく分からない部分がいくつかあります。

コードにならない部分は、言葉で説明しないと分からないとは、回答者としては言えるものの、なかなか、難しいことを考えていらっしゃるようです。

例えば、
>Range("H5:H100")
>この範囲に値の貼り付けで入力は禁止です、というか、禁止しないと
この範囲に値の貼り付けによる、入力は禁止という意味ですか?
でも、貼り付けはダメだということですね。でも、そのプログラムは、セルに2つ入れることを禁止すれば済むことですよね。一個のセルに対して、一個の値を入れることに関して、貼り付けはダメというプログラムも、可能ですが、わざわざそんな仕掛けを作る必要はありませんね。

質問内容から、サルルベージできたのは、以下のぐらいです。

Application.EnableEvents = False
 マクロで書き込みがある場合は、これで挟む
Application.EnableEvents = True

'///
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  '複数個所同時入力を拒否する?
  If ActiveSheet.AutoFilterMode Then Exit Sub
  'AutoFilterModeだったら拒否する
  If Not Intersect(Target, Range("B2:B3,D2:D3,H2:H3,J2:J3,H5:H100")) Is Nothing Then
   If VarType(Target.Value) <> vbLong Then MsgBox "整数のみしか入れられません", vbInformation: Exit Sub
  End If
End Sub
この回答への補足あり
    • good
    • 0
この回答へのお礼

ありがとうございます
定年延長で現場から総務に配属されました
ここではエクセルを使いこなせる人がいなくて、エクセルをワードのように使っているので
「もっと自動化できる」と言ってしまったのが始まりで四苦八苦しています
午後は暇で毎日エクセルやってます

マクロはダブルクリック処理なら、皆さんの回答を見て何となくできたのですが
Sub Worksheet_Change(ByVal Target As Range)だけは、いろいろなサンプルがあって理解できません

それで、数値を入力して、そのセルをダブルクリクして、その後の処理をワンパターンのIF文だらけの
マクロで処理しています

でもこれって、どう考えても、Sub Worksheet_Change(ByVal Target As Range)だろって
マクロができる人はすぐ思いますよね

毎日gooで答えを見ているのですが、ギブアップして、初めて質問させていただきました

貼り付け云々の件ですが
If Target.Count > 1 Then Exit Sub
'複数個所同時入力を拒否する?
のことだと思います。質問がヘタでした。すみません

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  '複数個所同時入力を拒否する?
  If ActiveSheet.AutoFilterMode Then Exit Sub
  'AutoFilterModeだったら拒否する
  If Not Intersect(Target, Range("B2:B3,D2:D3,H2:H3,J2:J3,H5:H100")) Is Nothing Then
   If VarType(Target.Value) <> vbLong Then MsgBox "整数のみしか入れられません", vbInformation: Exit Sub
  End If
  
  'ここからマクロ処理
  Application.EnableEvents = False
   'マクロ処理
  Application.EnableEvents = True

End Sub

上のマクロでやってみます。

お礼日時:2017/09/21 15:31

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

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


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング