Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
Floreo  
#1 Posted : Tuesday, May 14, 2019 1:04:28 AM(UTC)
Floreo

Rank: Newbie

Groups: Registered
Joined: 5/14/2019(UTC)
Posts: 3

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  
#2 Posted : Tuesday, May 14, 2019 11:27:51 AM(UTC)
Igor/10Tec

Rank: Administration

Groups: Administrators, Forum Moderator
Joined: 1/17/2011(UTC)
Posts: 1,053

Thanks: 14 times
Was thanked: 137 time(s) in 137 post(s)
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:

Code:
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  
#3 Posted : Wednesday, May 15, 2019 1:20:03 AM(UTC)
Floreo

Rank: Newbie

Groups: Registered
Joined: 5/14/2019(UTC)
Posts: 3

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  
#4 Posted : Wednesday, May 15, 2019 6:26:37 AM(UTC)
Igor/10Tec

Rank: Administration

Groups: Administrators, Forum Moderator
Joined: 1/17/2011(UTC)
Posts: 1,053

Thanks: 14 times
Was thanked: 137 time(s) in 137 post(s)
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  
#5 Posted : Wednesday, May 15, 2019 9:35:37 PM(UTC)
Floreo

Rank: Newbie

Groups: Registered
Joined: 5/14/2019(UTC)
Posts: 3

Thank you again. Much appreciated.
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.