View Full Version : [SOLVED:] VBA Target.value
Victor
01-24-2010, 02:01 PM
Hi:
Please need help with the "Target.value = 0" IF to clear the cell contents in the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = 0 Then
Target.Offset.Offset(0, 1).ClearContents
End If
If Target.Column = 1 Then
If Target.Row > 10 Then
If Target.Row < 15 Then
Application.EnableEvents = False
Target.Offset.Offset(0, 1) = Now()
Application.EnableEvents = True
End If
End If
End If
End Sub
Thanks for the help.
Victor
lucas
01-24-2010, 02:35 PM
Could this be the problem. You have:
Target.Offset.Offset(0, 1).ClearContents
Try:
Target.Offset(0, 1).ClearContents
Victor
01-24-2010, 06:08 PM
Lucas:
I did the correction suggested but I still can not run the IF section:
If Target.Value = 0 Then
Target.Offset(0, 1).ClearContents
End If
See corrected vba code below:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = 0 Then
Target.Offset(0, 1).ClearContents
End If
If Target.Column = 1 Then
If Target.Row > 10 Then
If Target.Row < 15 Then
Application.EnableEvents = False
Target.Offset.Offset(0, 1) = Now()
Application.EnableEvents = True
End If
End If
End If
End Sub
Thanks for the suggestion.
Victor
tpoynton
01-24-2010, 06:19 PM
the first if statement works for me, if I put a 0 in column A, anything in column B in the same row is cleared. Perhaps you need to exit the sub if the value is 0? try
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = 0 Then
Target.Offset(0, 1).ClearContents
Exit Sub
End If
If Target.Column = 1 Then
If Target.Row > 10 Then
If Target.Row < 15 Then
Application.EnableEvents = False
Target.Offset(0, 1) = Now()
Application.EnableEvents = True
End If
End If
End If
End Sub
Bob Phillips
01-25-2010, 01:34 AM
Try
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
Application.EnableEvents = False
If Target.Value = 0 Then
Target.Offset(0, 1).ClearContents
End If
If Not Intersect(Target, Me.Range("A11:A14")) Is Nothing Then
Target.Offset(0, 1) = Now()
End If
ws_exit:
Application.EnableEvents = True
End Sub
Victor
01-25-2010, 05:37 AM
Hi:
I am enclosed file with the code. May be there you can catch the problem.
Thanks for the help.
Victor
Bob Phillips
01-25-2010, 05:49 AM
My code supplied earlier works fine with that workbook. Why didn't you try it?
Victor
01-25-2010, 06:12 AM
XLD:
I just not want a solution but to learn what is the problem with the code that apparently should work.
I really appreciate your help.
Thanks.
Victor
mdmackillop
01-25-2010, 06:37 AM
You need to set EnableEvents to False before the code makes any changes
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exits
Application.EnableEvents = False
If Target.Value = 0 Then
Target.Offset.Offset(0, 1).ClearContents
End If
If Target.Column = 1 Then
If Target.Row > 10 Then
If Target.Row < 15 Then
Target.Offset(0, 1) = Now()
End If
End If
End If
Exits:
Application.EnableEvents = True
End Sub
Bob Phillips
01-25-2010, 06:42 AM
XLD:
I just not want a solution but to learn what is the problem with the code that apparently should work.
I really appreciate your help.
Thanks.
Victor
Then why didn't you say that rather than posting a workbook with the same code that you posted earlier.
When you change a value to 0 it just generates an endless cascade of changes. because you change the value to the right, which triggers the event again, and so on.
Victor
01-25-2010, 08:46 AM
mdmackillop and xlt:
I tried both xld code and now mdmackillop and when I run the code with Excel 2003 the code do not erase the offset(0,1) contents when the target.value = 0.
I posted the file so you can run it and check it out to see if it runs.
I am sorry if I do not explained myself clearly, it was not my intention, but still I really need your help as to why the suggested code do no run the clearcontents code.
Thanks for the help.
Victor
Victor
01-25-2010, 02:13 PM
Thanks to all:
If I add Goto Exits after the instruction below solves my problem for the codes suggested by mdmackillop and xlt:
Target.Offset.Offset(0, 1).ClearContents
Goto Exits
Without this intruction, in both suggested codes it shows the date (now()) when clear the cells contents. Which was my problem inicially.
Thanks to all for the help.
Victor
Bob Phillips
01-25-2010, 04:10 PM
Better to use
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto ws_exit
Application.EnableEvents = False
If Target.Value = 0 Then
Target.Offset(0, 1).ClearContents
ElseIf Not Intersect(Target, Me.Range("A11:A14")) Is Nothing Then
Target.Offset(0, 1) = Now()
End If
ws_exit:
Application.EnableEvents = True
End Sub
than to use Goto
mdmackillop
01-25-2010, 05:49 PM
Hi Victor,
Stepping through your original code would show the events occuring and allow you to see where the code was going wrong.
Regards
MD
Victor
01-26-2010, 01:44 AM
Another option is the code modified by tpoynton adding:
Target.Offset(0, 1).ClearContents
Exit Sub
Thanks all for your interest is providing different alternatives to solved the problem but the one provided by xld required less steps.
Victor
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.