VBA Code Worksheet_ Change

[FIRSTCHOICE]

Hey,

I am looking for some guidance on developing some code to do some worksheet change event code. I have a situation where I have 4 columns, (A,B,C,D). The values that are in these cells are all interconnected (you can find A with the value in D or value in C from the value in A). I am looking to create some code where I would only have to put one value in one of the cells and the other three on that row would compute. In addition, if they are all empty then it stops trying to compute and if there are values in them it stops.


I have decent VBA skills for a novice so if someone could help me get started I think I could manipulate it well.

Thanks,
HN

 

[Damon Ostrander]

Hi HN,

Yes, this is not hard to do with VBA, but obviously you must do the calculations/formulas in VBA rather than with worksheet functions.

You have to test for the column that has been edited in the Worksheet_Change event (the column is Target.Column) to determine whether it is A, B, C or D. Then disable events (Application.EnableEvents = False) to keep the output cell changes from causing the Worksheet_Change event from triggering recursively. Then calculate and set the values of the dependent column cells, then re-enable events (Application.EnableEvents = True).

Say the Target.Column = 4 (i.e., column D has changed) so now you want to calculate A in the same row as a function of the value of D. You would just set the A cell using

Cells(Target.Row,"A") = (the value)

Not so hard. I hope you find this helpful.

Damon