1. 10Tec Forum
  2. iGrid ActiveX
  3. ActiveX Grid Questions and Issues
  4. IGrid ActiveX7.0 - Updating Cells in Worksheet?
Floreo
2019-05-14T00:04:28Z
Hi all,

A newby question...

I have managed to establish an iGrid in my Excel Userform and populate it from a sheet range (A10:N33), which contains the source data.

I want to be able to edit cells in the iGrid and have the source data in my worksheet update at the same time.

What is the process to do this? I've had a look at the various CommitEdit events etc but not exactly sure how to apply them.
Given there are over 200 cells that can be edited, do edit events need to be specified for every single one by individual reference?

I'm not too fussy on validation, controls etc. simply about being able to type in a value and have it update in the spreadsheet.

Cheers.
Igor/10Tec
2019-05-14T10:27:51Z
The Edit Aircrafts Excel VBA sample for iGrid from iGrid_Samples.xls demonstrates how to edit Excel worksheet cells in iGrid. Actually, you can save changes in the underlying worksheet using one statement in an event handler of the BeforeCommitEdit event of iGrid:

Private Sub iGrid1_BeforeCommitEdit(ByVal lRow As Long, ByVal lCol As Long, eResult As iGrid700_10Tec.EEditResults, ByVal sNewText As String, vNewValue As Variant, ByVal lConvErr As Long, ByVal bCanProceedEditing As Boolean, ByVal lComboListIndex As Long)
   Cells(START_ROW + lRow - 1, START_COL + lCol - 1).Value = vNewValue
End Sub

START_ROW and START_COL are the row and column index of the top-left worksheet cell that defines the rectangle you upload to iGrid.

AfterCommitEdit works too, but you will need to retrieve the new cell values with the CellValue property of iGrid.
Floreo
2019-05-15T00:20:03Z
Many thanks... this was extremely helpful.
One more question if I may?

Some of the cells in the source worksheet that are shown in iGrid are derived from formulas in the worksheet.
If I change a value in iGrid and it correctly updates the worksheet, which in turn updates all formula driven values... how can I immediately reflect this with updated values for those cells in iGrid?

Is it a case of reloading the entire grid if a cell in the grid / worksheet is updated? This wouldn't be a major concern as the grid is relatively small.

Thanks again.
Igor/10Tec
2019-05-15T05:26:37Z
If you copied the Excel cell values into iGrid using the LoadFromArray method or manually by setting the CellValue properties, iGrid knows nothing about relations between your worksheet cells coded with formulas. The easiest way to update iGrid in this case, sure, is to reload it fully. This may work good for small grids, but for huge grids you may need to do a more intelligent uploading and copy only the changed cells to avoid any performance issues. You know what formulas and where you are using, and this will allow you to implement such an algorithm.
Floreo
2019-05-15T20:35:37Z
Thank you again. Much appreciated.
Similar Topics
Users browsing this topic
  1. 10Tec Forum
  2. iGrid ActiveX
  3. ActiveX Grid Questions and Issues
  4. IGrid ActiveX7.0 - Updating Cells in Worksheet?