Macro Runs when the user changes any cell on the work sheet
I want to run a macro (just makes sure the end user doesn't accidentally
delete some of the formulas from a column) when the user adds/changes any cell value. The idea is that they input the following on each line, and the Total column multiplies the unit cost by the quantity. Product Unit Cost £ Quantity Total However, if they add rows the formula disappears and the calculation doesn't work. I want to add a macro so when they add/change any of the left hand cells (even if they have added a row) the formula will appear in the totals column and complete the calculation. Any ideas? |
Macro Runs when the user changes any cell on the work sheet
Hello
Right-click on the worksheet tab, select View Code With columns starting from A to D, paste this sample code (with no exchaustive testing) and amend to your needs: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range(Target.Address), Range("A:C")) _ Is Nothing Then Dim r As Long r = Target.Row If Cells(r, "B").Value < "" And _ Cells(r, "C").Value < "" Then Cells(r, "D").Value = Cells(r, "B") * Cells(r, "C") End If End If End Sub HTH Cordially Pascal "raphiel2063" a écrit dans le message de news: ... I want to run a macro (just makes sure the end user doesn't accidentally delete some of the formulas from a column) when the user adds/changes any cell value. The idea is that they input the following on each line, and the Total column multiplies the unit cost by the quantity. Product Unit Cost £ Quantity Total However, if they add rows the formula disappears and the calculation doesn't work. I want to add a macro so when they add/change any of the left hand cells (even if they have added a row) the formula will appear in the totals column and complete the calculation. Any ideas? |
Macro Runs when the user changes any cell on the work sheet
Hi
Thanks for the macro, I was wondering if there was a way of making it more generic like if any row of column was changed the the calculation would be done (the real data has a lot more columns and rows in reality). Tom "papou" wrote: Hello Right-click on the worksheet tab, select View Code With columns starting from A to D, paste this sample code (with no exchaustive testing) and amend to your needs: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range(Target.Address), Range("A:C")) _ Is Nothing Then Dim r As Long r = Target.Row If Cells(r, "B").Value < "" And _ Cells(r, "C").Value < "" Then Cells(r, "D").Value = Cells(r, "B") * Cells(r, "C") End If End If End Sub HTH Cordially Pascal "raphiel2063" a écrit dans le message de news: ... I want to run a macro (just makes sure the end user doesn't accidentally delete some of the formulas from a column) when the user adds/changes any cell value. The idea is that they input the following on each line, and the Total column multiplies the unit cost by the quantity. Product Unit Cost £ Quantity Total However, if they add rows the formula disappears and the calculation doesn't work. I want to add a macro so when they add/change any of the left hand cells (even if they have added a row) the formula will appear in the totals column and complete the calculation. Any ideas? |
Macro Runs when the user changes any cell on the work sheet
OK. I wasn't sure if I could just say something like if any column was change
(A:Z) then it would trigger the calculation. Thanks. "papou" wrote: Tom The sample code has no limits in rows, it just checks for the relevant values in columns to achieve your calculation. You can easily amend with the relevant range of columns (A to IV for instance). But you will need to identify both Unit Cost and Quantity columns (so it can't be that generic). HTH Cordially Pascal "raphiel2063" a écrit dans le message de news: ... Hi Thanks for the macro, I was wondering if there was a way of making it more generic like if any row of column was changed the the calculation would be done (the real data has a lot more columns and rows in reality). Tom "papou" wrote: Hello Right-click on the worksheet tab, select View Code With columns starting from A to D, paste this sample code (with no exchaustive testing) and amend to your needs: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range(Target.Address), Range("A:C")) _ Is Nothing Then Dim r As Long r = Target.Row If Cells(r, "B").Value < "" And _ Cells(r, "C").Value < "" Then Cells(r, "D").Value = Cells(r, "B") * Cells(r, "C") End If End If End Sub HTH Cordially Pascal "raphiel2063" a écrit dans le message de news: ... I want to run a macro (just makes sure the end user doesn't accidentally delete some of the formulas from a column) when the user adds/changes any cell value. The idea is that they input the following on each line, and the Total column multiplies the unit cost by the quantity. Product Unit Cost £ Quantity Total However, if they add rows the formula disappears and the calculation doesn't work. I want to add a macro so when they add/change any of the left hand cells (even if they have added a row) the formula will appear in the totals column and complete the calculation. Any ideas? |
All times are GMT +1. The time now is 11:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com