If cell active then change value in another cell

[thadacto]

Please Login or Register  to view this content.

The value of cell L49 =1, L50=2 and L51 =3

Depending what number is in cell P50, affects the balance of a column of figures Column Q.
Up to now, I have been changing the number (1,2,3) in P50 to reflect what I want as this works with an If formula in Q50.
However, If I could just click in one of the cells in Col L that would change value of the cell P50, I would not have to change manually.

Any thoughts on where I am wrong in the above code.

Many thanks.

[jomili]

thadacto,

I thought I had a solution, but then realized I don't understand the problem. Are you wanting to:
1) Activate the sheet (coming from another sheet)
2) See what cell has been activated (not what cell you activate, but the last cell clicked on that sheet before you reactivated it)
3) Depending on that, change the values in P50?

Or, are you wanting to:
1) Activate (select) a cell on the active sheet
2) Depending on that selection, change the values in P50?


If the second one is right, I think you;d need to use a Worksheet change event:

Please Login or Register  to view this content.

[thadacto]

Thanks for the reply, jomili.

Yes, it is your second suggestion. Only dealing with Sheet2.
When I select (by clicking in it with the mouse) Cell L49, I want P50 to change to a value of 1
When I select cell L50, I want the value in P50 to be 2
When I select cell L51, I want the value in P50 to be 3 .... and so on.

When any other cell on the sheet is selected it has no effect on P50 (which obviously it won't because it is not in the code to do something!

Hope this is clearer.

[jomili]

here's the basic code you need:

Please Login or Register  to view this content.

You'd paste this into a sheet module, not a standard module (ie in the VBA editor double-click on the sheet tab you want to use this in). You'd also need to add your various "ElseIF" statements for any other actions.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[thadacto]

Many thanks for all the help, jomili and JOHN H.

jomili, I didn't get your code to work but I'll get back to it and see if I can.

JOHN H. Your piece of code worked great, and even allows for going much further down the "L" column.
Many thanks indeed.

[JOHN H. DAVIS]

You're welcome. Glad to help out, and thanks for the feedback.