Worksheet_change event

[eggpap]

Hello,

I have a workbook (let's call it "MyWb") with many udf and sheets i
it.
To simplify the issue suppose I have the following code i
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 th
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 i
claimed.
I have debugged with two breakpoints placed on the third and fourt
statement on the sample snippet. The program stops correctly on th
first bp. Then, if I press F8 to do another step, the program stop
internally to one udf, the, if I press F5 to complete the run, th
program ignores the second bp and I get nothing.

Thanks,
Emiliano
Excel 2003 on Vista HP
VBA knowin

 

[JBeaucaire]

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 The
Target.Offset(1) = "x
End I
End Su
===========

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

 

[Simon Lloyd]

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

Hello

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

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

or: when a cell of 7th row changes its value, valorize th
correspondin
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 i
claimed
I have debugged with two breakpoints placed on the third and fourt
statement on the sample snippet. The program stops correctly on th
first bp. Then, if I press F8 to do another step, the program stop
internally to one udf, then, if I press F5 to complete the run, th
program ignores the second bp and I get nothing

Thanks
Emilian
Excel 2003 on Vista H
VBA knowin

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[eggpap]

You need to add a "then" to the end of the If statement:

Sorry I mistaken the post writing, consider the snippet correctl
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 afte
the first run the change event is no more triggered, I have to close an
to reopen the wb to continue to test. With or without enableevents th
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 cleare
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]

eggpap, why not attach a workbook here so we can actually see what th

Sorry I mistaken the post writing, consider the snippet correctl
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 cod
but nothing happens. The enableevents=true is never executed and afte
the first run the change event is no more triggered, I have to close an
to reopen the wb to continue to test. With or without enableevents th
code is not completely executed: some udf intercepts it

Thank
===========
Private Sub Worksheet_Change(ByVal Target As Range
If Target.Row = 7 The
Target.Offset(1) = "x
End I
End Su
===========

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

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[eggpap]

Enless you are using (and i suspect you are) "On Error Resume Next" you
code should not run at all as its ill formed, its usually best t
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 cod
of the project. But after this first modify the valorization of the 8t
row was not still made. I had to use the target.offset(1).clear befor
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]

Using On Error Resume Next is not a practice i advocate as it ignore
all errors which is why your code just ran without any hitches, ther
are many ways of capturing and using error handling, as for the clea
question, without seeing your workbook and what you are trying t

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 cod
of the project. But after this first modify the valorization of the 8t
row was not still made. I had to use the target.offset(1).clear befor
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 Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[Gary''s Student]

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

 

[Simon Lloyd]

Is your query solved

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[eggpap]

Help
If your query has been solved please take a moment to let us an
eveyone else know by going to, Thread Tools > "Mark This Thread A
Solved", shown by this icon

Thanks to all,

I've simply adopted other solutions. I was not able to solve th
specific query.
I surrended. I'll retry later when less busy.

Emilian