When I start my form all records are marked as edited (little pencil icon on the left). When I comment out the code in my AfterCellUpdate event this doesn't happen.
I have an unbound column that contains value based on a bound column.
During
InitializeRow
e.row.cells("UnboundCol").value = e.row.cells("BoundCol").value * 1.1
... and during
AfterCellUpdate
if e.cell.column.key = "UnboundCol" then
e.cell.row.cells("BoundCol").value = e.cell.row.cells("UnboundCol").value / 1.1
end if
Am I doing it wrong?
I would probably use BeforeRowUpdate, since the data doesn't really have to be updated from the unbound column to the bound column until the row is committed.
If you set the cell value of the bound column to the same value it is already set to, it should not dirty the row. At least, I don't think it will. If it does, then the solution to that is to simply compare the values before you set it and don't set it if it's already correct.
I'll use a real example to explain what I'm doing here.
The bound column is in Euros and is hidden
The unbound column is in US Dollars and is editable
When the user enters a value it will be in US Dollars. The system needs to convert from USD to Euro and update the bound column.
Unfortunatly, when it updates the bound column it also fires the AfterCellUpdate event which refreshes the unbound column which could potentially update the bound column, etc...
Is there a better way to do this?
Hi,
Hm, this code is a bit unusual. So the Unbound column is 1.1 times the Bound column and the bound column is the unbound column divided by 1.1? That's going to create a sort've circular reference here. Why keep both values calculated continuously?
Using AfterCellUpdate to update the value of a cell is usually not a good idea, since this will cause the event to fire again. In your case, it seems okay, since you are only checking for one particular column. So the second time the event fires, nothing happens.
If you just want to get rid of the pencil, then what you need to do is commit the changes to the row. You do that by calling Update on the row after setting the cell value.