| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why macro runs on the same sheet? | Excel Discussion (Misc queries) | |||
| Cerfiticate Issues When User runs my Macro | Excel Discussion (Misc queries) | |||
| Why is Sheet deleted when Macro runs... | Excel Programming | |||
| User to decide how often a macro runs | Excel Discussion (Misc queries) | |||
| Run macro but let user update spreadsheet while it runs | Excel Programming | |||
Linear Mode
