Run a Macro when clicking on any cell in a Table Column

[Petronella]

I have a Table where each row represents a past or forthcoming charity event. Several of the columns are cost columns derived from lookup tables via Index/Match.

It works great until there is a rate change in one of the costs (maybe the cost of hiring a particular room changes from say $100 per day to $200 per day).

So I want to be able to fix the values of completed events so that their Index/Match values don't automatically update when the rate changes.

I have written a macro that will Copy, Paste Values for the entire table row when called from the first column of a row. This 'fixes' the values on the row such that the original values are retained when the rate changes. However it involves clicking in the first cell of the row in question and then calling the macro.

Is it possible for the macro to run automatically for any row in the table whenever a cell in that first column is clicked?

Apologies, that's rather long winded!

 

[Peter_SSs]

Is it possible for the macro to run automatically for any row in the table whenever a cell in that first column is clicked?

That is possible but sounds risky. If you went to enter a new future event I presume that you would click that first column to enter something at which time the whole row would get 'fixed' before the formulas could update.

Instead, what about double-clicking that first column when you are ready to 'fix' it? Something like this ..
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Columns("A")) Is Nothing Then '<- Check column is correct
    With Intersect(Target.EntireRow, ActiveSheet.UsedRange)
      .Value = .Value
    End With
  End If
End Sub

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Petronella]

That is possible but sounds risky. If you went to enter a new future event I presume that you would click that first column to enter something at which time the whole row would get 'fixed' before the formulas could update.

Instead, what about double-clicking that first column when you are ready to 'fix' it? Something like this ..
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Columns("A")) Is Nothing Then '<- Check column is correct
    With Intersect(Target.EntireRow, ActiveSheet.UsedRange)
      .Value = .Value
    End With
  End If
End Sub

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hey Peter

So neat and so simple. The perfect solution. Copied and pasted and it worked immediately. And I understand how it works too. Result! Thanks so much.

John

[Every day's a learning day.]

PS: I have updated my Account details as you suggested].

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and for updating your details.