Thread: Worksheet change event. Puzzling case. VBA

Hi there,

I'm not sure if this is inherent in VBA or not, but whenever I invoke a change in a cell value, VBA does not necessarily calculate based on decisions. Please see the attached for description.

In "Test" worksheet, if you go to H4 and type in 8/4/2011, the last column, which is "analyzable denominator" will be "yes" because col H has value and that value falls in the current fiscal year. (this is working based on decisions written in VBA)

However, if you change the col H to 8/4/2012 in the formula bar and press enter, while col O and P changes accordingly, the last col remains "yes".

I have found that the last col changes only if you have invoked changes in other cells on the same row. For example, if you change 8/4/2012 back to 8/4/2011 AND also change another cell value on the same row, then the last col will change from "No" to "Yes".

Does this have to do with how worksheet change event is built in? or does this have to do with how VBA code is written?

Appreciate any help

Thank you

I am little confused

your first part of the code is

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column > 35 Then Exit Sub
    Application.EnableEvents = False

what is your target.column that is on change of cells in which column the event should be fired
I guess it should be column H because it is in this column you change the entry and not in any other column
column H is the 8th column

so your first line should be

If Target.Column <> 8 Then Exit Sub
    Application.EnableEvents = False

try with this change and see whether you get what you want

if you think it works then

your reference to

Cells(Target.Row, 8)

which is nothing but target itself
so you can very well replce this with

target

I think this is the problem if necessary post back.

for easy understanding it is better to use the column letter than the column nummber because you have to find out which column refers to 25th columnd

for e.g. you can write

if target.colum<>range("H1").column then exit sub

similarly

the line

Cells(Target.Row, 25).Value = "No"

can be changed to

Cells(Target.Row, "Y").Value = "No"

even that

.value

is not necessary as default property of one cell range is its value

what you have written is not wrong but this change will help you in debugging.