Worksheet_change event
 

Hello,

I have a workbook (let's call it "MyWb") with many udf and sheets in
it.
To simplify the issue suppose I have the following code in
Worksheet_change event of Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 7
target.offset(1)="x"
end if
End Sub

or: when a cell of 7th row changes its value, valorize the
corresponding
column cell on the 8th row with the value "x"

This code performs correctly on an empty wb. On MyWB project, instead,
nothing happens, the offset(1) cell is not valorized and no error is
claimed.
I have debugged with two breakpoints placed on the third and fourth
statement on the sample snippet. The program stops correctly on the
first bp. Then, if I press F8 to do another step, the program stops
internally to one udf, the, if I press F5 to complete the run, the
program ignores the second bp and I get nothing.

Thanks,
Emiliano
Excel 2003 on Vista HP
VBA knowing


--
eggpap
------------------------------------------------------------------------
eggpap's Profile: http://www.thecodecage.com/forumz/member.php?userid=90
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48998

Worksheet_change event
 

You need to add a "then" to the end of the If statement:
============
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 7 Then
Target.Offset(1) = "x"
End If
End Sub
============

Also, you should watch for instances when the cell in row 7 was cleared
of data and clear the matching row 8 as well, just a suggestion:
============
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo 20
If Target.Row = 7 Then
If Target = "" Then
Target.Offset(1) = ""
Else
Target.Offset(1) = "x"
End If
End If
20
End Sub
============


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48998

Worksheet_change event
 

Enless you are using (and i suspect you are) "On Error Resume Next" your
code should not run at all as its ill formed, its usually best to
compile your code before adding error handling, your code is missing the
GOTO which in this case is THEN like this:
Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 7 *Then
*Target.Offset(1) = "x"
End If
End Sub
--------------------
eggpap;177148 Wrote:
Hello,

I have a workbook (let's call it "MyWb") with many udf and sheets in
it.
To simplify the issue suppose I have the following code in
Worksheet_change event of Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 7
target.offset(1)="x"
end if
End Sub

or: when a cell of 7th row changes its value, valorize the
corresponding
column cell on the 8th row with the value "x"

This code performs correctly on an empty wb. On MyWB project, instead,
nothing happens, the offset(1) cell is not valorized and no error is
claimed.
I have debugged with two breakpoints placed on the third and fourth
statement on the sample snippet. The program stops correctly on the
first bp. Then, if I press F8 to do another step, the program stops
internally to one udf, then, if I press F5 to complete the run, the
program ignores the second bp and I get nothing.

Thanks,
Emiliano
Excel 2003 on Vista HP
VBA knowing


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48998

Worksheet_change event
 

JBeaucaire;177153 Wrote:
You need to add a "then" to the end of the If statement:


Sorry I mistaken the post writing, consider the snippet correctly
performing.
It runs correctly on an empty wb.
This problem is similare to that posted on
'This Post' (http://tinyurl.com/7ao6ur)
I tried to place the Application.enablevents before and after the code
but nothing happens. The enableevents=true is never executed and after
the first run the change event is no more triggered, I have to close and
to reopen the wb to continue to test. With or without enableevents the
code is not completely executed: some udf intercepr it.

Thanks
============
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 7 Then
Target.Offset(1) = "x"
End If
End Sub
============

Also, you should watch for instances when the cell in row 7 was cleared
of data and clear the matching row 8 as well, just a suggestion:
============
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Row = 7 Then
If Target = "" Then
Target.Offset(1) = ""
Else
Target.Offset(1) = "x"
End If
End If
End Sub
============


--
eggpap
------------------------------------------------------------------------
eggpap's Profile: http://www.thecodecage.com/forumz/member.php?userid=90
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48998

Worksheet_change event
 

eggpap, why not attach a workbook here so we can actually see what the
problem is.eggpap;177156 Wrote:
Sorry I mistaken the post writing, consider the snippet correctly
performing.
It runs correctly on an empty wb.
This problem is similar to that posted on
'This Post' (http://tinyurl.com/7ao6ur)
I tried to place the Application.enablevents before and after the code
but nothing happens. The enableevents=true is never executed and after
the first run the change event is no more triggered, I have to close and
to reopen the wb to continue to test. With or without enableevents the
code is not completely executed: some udf intercepts it.

Thanks
============
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 7 Then
Target.Offset(1) = "x"
End If
End Sub
============

Also, you should watch for instances when the cell in row 7 was cleared
of data and clear the matching row 8 as well, just a suggestion:
============
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Row = 7 Then
If Target = "" Then
Target.Offset(1) = ""
Else
Target.Offset(1) = "x"
End If
End If
End Sub
============


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48998

Worksheet_change event
 

Simon Lloyd;177154 Wrote:
Enless you are using (and i suspect you are) "On Error Resume Next" your
code should not run at all as its ill formed, its usually best to
compile your code before adding error handling

Thanks Simon and JBeaucaire,

your tips was very helpfull.
I compiled before then placed the On Error Resume Next in the code.
Strangely the code was executed step by step without jump to other code
of the project. But after this first modify the valorization of the 8th
row was not still made. I had to use the target.offset(1).clear before
to use the target.offset(1)="x" statement.

It was a mistery for me. I've too much to learn.
Why using On Error the code is completely executed?
Why clearing the cell before, valorize it correctly?


--
eggpap
------------------------------------------------------------------------
eggpap's Profile: http://www.thecodecage.com/forumz/member.php?userid=90
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48998

Worksheet_change event
 

Using On Error Resume Next is not a practice i advocate as it ignores
all errors which is why your code just ran without any hitches, there
are many ways of capturing and using error handling, as for the clear
question, without seeing your workbook and what you are trying to
achieve i couldn't commenteggpap;177160 Wrote:
Thanks Simon and JBeaucaire,

your tips was very helpfull.
I compiled before then placed the On Error Resume Next in the code.
Strangely the code was executed step by step without jump to other code
of the project. But after this first modify the valorization of the 8th
row was not still made. I had to use the target.offset(1).clear before
to use the target.offset(1)="x" statement.

It was a mistery for me. I've too much to learn.
Why using On Error the code is completely executed?
Why clearing the cell before, valorize it correctly?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48998

Worksheet_change event
 

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 7 Then
Application.EnableEvents = False
Target.Offset(1).Value = "x"
Application.EnableEvents = True
End If
End Sub
--
Gary''s Student - gsnu2007k

Worksheet_change event
 

Is your query solved?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48998