PDA

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

xld
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

xld
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

xld
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

xld
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