エクセルVBAのChangeイベントの書き方について

質問

またまた質問させてもらいます。
先ほど「エクセルで作業した日の日付を残す」で質問させて
頂いた件での引き続きなのですが、
セルA1～セルL1までのそれぞれのセルに「1」を入力する
作業が有るとします。（1が入力されるか、されないかは任意で
また、入力する時刻も異なる）
そして、「1」が入力された、その時の時刻をセルA2～セルL2
に表示させるにはどうのようにVBAを書けばいいのでしょうか？
セルA1に「1」が入力された場合はA2に表示されるという風に
それぞれのセルの下に表示されるという形なのですが。
ちなみに先ほど回答いただいたVBAを少しいじってみて
以下のようなものを作ってみたのでが、これだと常にA1のセルに
「１」が入っているので、毎回時刻が更新されてしまうみたいで、、
この繰り返しを書いてもダメなのですね。
ご回答のほど宜しくおねがいしますーーーー

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("a1") = "1" Then
Range("a1").Value = Now()
End If
End Sub

回答No.1

merlionXX · ベストアンサー

こんな感じでしょうか。

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:L1")) Is Nothing Then
Exit Sub
Else
If Target.Value = 1 Then
Target.Offset(1, 0) = Now()
Else
Target.Offset(1, 0) = ""
End If
End If
End Sub

コピペやドラッグ等でいっぺんに複数セルへの入力があるのであれば、以下です。

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:L1")) Is Nothing Then
Exit Sub
Else
If Target.Count = 1 Then
If Target.Value = 1 Then
Target.Offset(1, 0) = Now()
Else
Target.Offset(1, 0) = ""
End If
Else
For Each c In Selection
If c.Value = 1 Then
c.Offset(1, 0) = Now()
Else
c.Offset(1, 0) = ""
End If
Next
End If
End If
End Sub

お礼

まったくの素人にVBAをこのように作成して
いただきありがとうございます。
まだ、悪戦苦闘中ですが、もう少し勉強して
的を得た質問が出来るよう勉強して出直して
きます。
ありがとうございます。

補足

お返事ありがとうございますーーーーー
早速VBAを利用しようとしているのですが、、、（泣）
全く反応がありません（泣）
ご回答いただいたVBAが問題ではなくて、自分のPCのエクセルの
問題っぽいのですが。
VBAが全くの素人のため、いろんな所を触ってしまって、設定
をおかしくしてしまいましたのでしょうか？
何処をどうしてしまったのか、全く分からないのですが、
どうしたらよういのでしょうか？
何かアドバイスいただけないでしょうか

回答No.5

merlionXX

> ツール>>マクロ>>visual basic editorを選択
> VBAProject(test.xls)>>Microsoft Excel Objects>>ThisWorkbook
> をダブルクリック

#1のmerlionXXです。
やはり・・・・。

ThisWorkbookではなく、Sheet1 (Sheet1) のようになっているシートのモジュールに貼り付けてください。（該当するシートのところにです。）

お礼

ごめんなさい。本当にこんな事で、皆さんに時間を取り
お返事をくださいまして、本当にありがとうございました。
これからも勉強して頑張って理解していきますので
その時はまたよろしくお願いいたします。
ありがとうございましたーm(..)m

回答No.4

Wendy02

こんにちは。

一応、デフォルトでは、Option Explicit は強制されていたはずですが、それがなければエラーが出ますから、よほどの初心者でなければ気がつきます。一応、「1 」を入れて、日時を出すのだから、セルは複数は扱わないことになるかと思います。

それはともかくとして、Changeイベントだから、二重起動は気をつけないといけません。
私は、こうした組み込み型のイベント・ドリブン・マクロは、最近はほとんど書きません。ご質問者さんの言うように、トラブったときの復活のしようがありません。本来はインスタンスを設けたほうがよいのですが、それはちょっとややこしいです。

とりあえず、でなくなったら、ReGain を実行してください。

Private Sub Worksheet_Change(ByVal Target As Range)
　　If Intersect(Target, Range("A1:L1")) Is Nothing Then Exit Sub
　　If Target.Count > 1 Then Exit Sub　'セルはひとつ
　　If Not IsNumeric(Target.Value) Then Exit Sub '数値を強制

　　Application.EnableEvents = False
　　If Target.Value = 1 Then
　　　　Target.Offset(1).Value = Now '日付と時間込み
　　Else
　　　　Target.Offset(1).ClearContents
　　End If
　　Application.EnableEvents = True
End Sub

'おまけ
Sub ReGain()
　'イベント復活
　Application.EnableEvents = True
End Sub

お礼

皆様、本当に色々なアドバイスをご返答くださいまして
ありがとうございます。
状況が変化しましたのでご連絡させていただきます。
皆様が送っていただいたVBAコードを
VBAProject>>Microsoft Excel Objects>>sheet1
の方に貼り付けましたら。
エクセルシート１のA1のセルに「１」と入力したところ
動作の確認がありました。
ThisWorkbookの方ではダメなんですね。
その辺の所もまだまだ理解できていなく、皆様にお時間
取っていただき返答していただいた事に、本当に感謝しており
ます。ありがとうございます。

回答No.3

merlionXX

> 全く反応がありません（泣）

#1のmerlionXXです。
標準モジュールではなく、シートのモジュールに記述しているのですよね？
残念ながらこちらではまったく状況がわかりません。

一度、パソコン自体を再起動してから、新しいファイルで再度わたしのコードを試してみていただけますか？

Option Explicit を宣言するなら、わたしの二つ目のコードの場合、#2さんご指摘の通り、2行目に
Dim c As Range
を挿入してください。

お礼

何度もご親切にお返事ありがとうございます。
コードを書き込んで居る場所は
エクセルを立ち上げ、ファイルname：testで保存
そこから、エクセルのツールバーから
ツール>>マクロ>>visual basic editorを選択
VBAProject(test.xls)>>Microsoft Excel Objects>>ThisWorkbook
をダブルクリック
右にコード記入欄が表示され
一行目に
Option　Explicit
と表示され、
その下に頂いた、VBAを貼り付け
その後エクセルシートに戻り、A1セルに「１」と入力
と言う操作を行っておりますが、「１」と入力した後
何も変化が無いと言う状況です。
コードは教えて頂いたとおり、2行目には
Dim c As Range
を入れてあります。
何かお気づきの点などありましたら、また、お返事いただけれ
ばと思います。
お時間を取っていただきありがとうございます。

回答No.2

nishi6

QNo.2931377「エクセルで作業した日の日付を残す」でのANo.2の

Private Sub Worksheet_Change(ByVal Target As Range)
　　If Target.Cells(1) = Range("a1") Then
　　　　Target.Cells(1, 2).Value = Now()
　　End If
End Sub

は質問の『A1のセルに「1」入力した時の日付、時刻をA2セルに表示したい』ということを満足しません。

解答(ANo.2)は、
『入力したセル範囲の左上の値と、セルA1の値が等しかったら、入力したセル範囲の左上の右のセルに日付、時刻を表示する』ようになっており、質問の答えにはなっていません。

例えば、A1に「ある値X」が入力されていれば、別のあるセルに「X」と入力すれば、その右に日付、時刻を表示したり、A1に何も入力されていないときは、別のあるセルの値を消去すれば、消去したセルの右に日付、時刻を表示したりします。早々に締め切られたので、コメントのしようがなかったです。

Private Sub Worksheet_Change(ByVal Target As Range)
　　If Range("a1") = "1" Then
　　　　Range("a1").Value = Now()　‘Range("a2").Value = Now()
　　End If
End Sub

これは、A1に最初、１を入れると、A1を書き換えてしまいます。後、別のセルに何を入力しても何も起きません。Range("a2").Value = Now() とすべきですが、A列しか変更していません。

まず、いろいろ作業する前にバックアップしておきます。
まずは、QNo.2931377解答(ANo.2)のモジュールまたはこのモジュールを修正したモジュールは削除してしまうべきでしょう。

ANo.1 merlionXXさんのモジュールは正しく動くと思いますが、問題があるとすれば、Option Explicit が宣言されていないようなので、
　　For Each c In Selection　で止まる可能性があります。これは、tmgolfさんのVBEの設定にも関係しますが、
　　Private Sub Worksheet_Change(ByVal Target As Range)
　　　　Dim c As Range
　　　　If Intersect(Target, Range("A1:L1")) Is Nothing Then
と、c を宣言した方がいいでしょう。

＞何処をどうしてしまったのか、全く分からないのですが、
＞どうしたらよういのでしょうか？
　　解答者も、なおさら分からないでしょう・・・
　　新しいExcelにmerlionXXさんのモジュールを貼り付けなおすのが最適ではないかと思います。また、エラーが起きたら、[実行→リセット]を行なわないと、何も反応しなかったりします。
　　Option Explicit は設定しておく方がいいでしょう。
　　VBE画面で、ツール→オプション、編集タブで、「変数の宣言を強制する」にチェックします。

お礼

ご返答ありがとうございます。
おっしゃるとおり、何が悪いか解らない
と質問しても、回答する方も回答のしようが
ないですね。
ど素人のため、的確な質問ができない中
お返事を頂き、ありがとうございます。
もう少し状況を説明させていただきますと
最初は
Private Sub Worksheet_Change(ByVal Target As Range)
　　If Target.Cells(1) = Range("a1") Then
　　　　Target.Cells(1, 2).Value = Now()
　　End If
End Sub

のVBAと

Private Sub Worksheet_Change(ByVal Target As Range)
　　If Range("a1") = "1" Then
　　　　Range("a1").Value = Now()　‘Range("a2").Value = Now()
　　End If
End Sub
のVBAも目的は達成は果たしてないにもしろ、反応はしており
動いていたのですが、あれこれ試行錯誤しているうちに
動作してないような感じになりました。
A1セルに「１」と入力するとB2セルもしくはA2セルにはその時
の時刻が表示されていたのですが、それすら出なくなったと
言うのが、今現在の状況です。
もう少し勉強して、もっと的確な質問を出来るようがんばります。
このような素人の質問に対して、アドバイスしていただき
ありがとうございます。