InitValue Function · 10Tec Forum

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

RRO
Newbie Topic Starter

2013-10-30T18:31:26Z

Hi all,

I'm quite new in vba and I have a question regarding the InitValue Function. I'm working on vba, on a userform with the iGrid activex control (Excel 2010).

I want to set an initial value to a specific cell (from a 10x10 database) and I want the user to be able to click on this cell and change the value. Then, before closing the window I will save this new value and send the update to the database.

Could anyone help me on this issue?

I first thought about something like that but it actually doesn't work...


Sub BeforeCommitEdit( _
   ByVal lRow As Long, _
   ByVal lCol As Long, _
   ByRef eResult As EEditResults, _
   ByVal sNewText As String, _
   ByRef vNewValue As Variant)
   
    iGrid1.ColCount = 10
    iGrid1.RowCount = 10
    
    iGrid1.InitValue(igCellText).CellValue(2, 2) = "Initial Value"
    
    'And if I try the following, the user can't edit the cell
    iGrid1.CellValue(2, 2) = "Initial Value"

End Sub

Thank you!

RRO.

Igor/10Tec
Administration

2013-10-31T08:35:10Z

It seems, the InitValue function is needed in this scenario. It just returns the default value iGrid uses by default for the corresponding cell type, and you cannot use it the way you showed in your code.

The BeforeCommitEdit event can be used in a scenario like yours, but it is a cell-level editing event. It is raised when the user is trying to save the changes in a CELL but not the WHOLE GRID. Obviously, it's not the case if you want to store user changes on form closing, but it may be something you need if you want to pass user changes into a memory recordset and then save allchanges at one go on form closing using a recordset's method. All depends on your data layer.

You can tell us more what you are doing in your project on this forum, or send us your project via email to our support address - then we'll be able to suggest you a solution.

RRO
Newbie Topic Starter

2013-10-31T09:37:01Z

Hi Igor,

Thx for your quick Reply. Here's the idea of my project (See attached pics):

1 - I have a database in an Excel Sheet with 3 columns and 20 rows.
2 - I want to fill the grid with this values (as initial display)
3 - Then, I need the user to be able to change the content of a cell if wrong (the "A321" cell for example is wrong).
4 - Of course, he must also be able to fill an empty cell with data
5 - Then, when he is done, he clicks the "Record" CmdButton and I save the whole grid in the same Excel sheet to update values.

Actually, I'm not sure what function could I use to:
1 - Create the grid (as a 3x20 table)
2 - Display initial values and save the updates after modification in each cell OR at the click on the Record Button.

Thank you for your help on the code for these actions.

RRO

RRO attached the following image(s):

2013-10-31 10_38_02-Microsoft Excel.xlsm.png

2013-10-31 10_31_53-Microsoft Excel.xlsm.png

Igor/10Tec
Administration

2013-10-31T11:47:13Z

I have just created an example for myself to write some code for you :)

As for the first part of the task, I populated the grid using the following code (the full listing of the form's module):

Option Explicit

Private Const START_COL As Long = 2
Private Const END_COL As Long = 4
Private Const START_ROW As Long = 2

Private Sub UserForm_Initialize()
   Dim iCol As Long
   Dim iRow As Long

   With iGrid1
      .BeginUpdate
      
      For iCol = START_COL To END_COL
         .AddCol sHeader:=Cells(1, iCol).Value
      Next
      
      iRow = START_ROW
      Do While Cells(iRow, START_COL).Value <> Empty
         .AddRow
         
         For iCol = START_COL To END_COL
            .CellValue(.RowCount, iCol - START_COL + 1) = Cells(iRow, iCol).Value
         Next
         
         iRow = iRow + 1
      Loop
      
      For iCol = 1 To .ColCount
         .AutoWidthCol iCol
      Next
      
      .EndUpdate
   End With
End Sub

As for the second part (editing and saving values), I have some questions:

1) Do you need to save the new cell value after the cell has been edited? Then, I guess, we do not need the Record button at all.

2) Can we add/remove rows to the original Excel database?

3) What validation of new input values are you going to use?

RRO
Newbie Topic Starter

2013-10-31T13:46:32Z

Thx a lot Igor, the code works perfectly! You're awesome! Even the Header set up and the automatic number of row are fine! (See attached).

For the Second part of the project:

Quote:

As for the second part (editing and saving values), I have some questions:

1) Do you need to save the new cell value after the cell has been edited? Then, I guess, we do not need the Record button at all.

Yes, it would be better to remove the Record Button and save the User modifications after each cell update.

Quote:

2) Can we add/remove rows to the original Excel database?

Nope... I'd prefer we don't change the Original Database because it's a very big one (The pic I sent you was just a very small part of it). Why?

Quote:

3) What validation of new input values are you going to use?

It's gonna be "Double", "Text" or "Date" format validation.

Thank's again!

RRO

RRO
Newbie Topic Starter

2013-10-31T13:48:33Z

And now, with your code, the user can change the cell value. We just have to save it back in the database.

Here's the attached...

Thx!

RRO attached the following image(s):

2013-10-31 14_37_45-Microsoft Excel.xlsm.png

Igor/10Tec
Administration

2013-11-01T08:16:56Z

Then this is very simple and is done with one call in the BeforeCommitEdit event:

Private Sub iGrid1_BeforeCommitEdit(ByVal lRow As Long, ByVal lCol As Long, eResult As iGrid500_10Tec.EEditResults, ByVal sNewText As String, vNewValue As Variant, ByVal lConvErr As Long, ByVal bCanProceedEditing As Boolean)

   Cells(START_ROW + lRow - 1, START_COL + lCol - 1).Value = vNewValue

End Sub

Note that you even do not need to check whether the input value is in the proper format - iGrid will check that automatically and display the "Invalid value: Type mismatch" message if it cannot convert the entered value into the type of the original cell value (date, number, etc).

This is the default behavior, but you can turn it off and process input errors by yourself. For more info, see the SilentValidation property.

Igor/10Tec
Administration

2013-11-01T08:22:53Z

The next step could be adding your custom validation rules to implement some "business logic". Here is an example in which we allow the user to enter only predefined values into the Aircraft Type column:

Private Sub iGrid1_BeforeCommitEdit(ByVal lRow As Long, ByVal lCol As Long, eResult As iGrid500_10Tec.EEditResults, ByVal sNewText As String, vNewValue As Variant, ByVal lConvErr As Long, ByVal bCanProceedEditing As Boolean)

   ' Example of value check
   If iGrid1.ColHeaderText(lCol) = "Aircraft Type" Then
      If (sNewText <> "A318") And (sNewText <> "B737") Then
         MsgBox "Aircraft type not allowed"
         eResult = igEditResProceed
         Exit Sub
      End If
   End If
   
   ' Save to database
   Cells(START_ROW + lRow - 1, START_COL + lCol - 1).Value = vNewValue

End Sub

Sure, it's a bad example - we should not use column header text in it (constant column keys are much better for that), or it would be better to use a combo list with predefined values for this column, but this example shows you the main idea.

Edited by user 2013-11-04T07:11:15Z | Reason: Not specified

RRO
Newbie Topic Starter

2013-11-12T08:34:03Z

Thank you Igor! It works perfectly! The table is cool and looks great now. I'm glad you take the time to read and answer my questions!

Thx again,

RRO.

Important Information:

The 10Tec Forum uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close