I am very new to vba and came accross iGrid as it gives me more options as it looks like for having a userform in a list view for data entry, as I am working on a recipe application
I am able to load the iGrid by using a loop and filling it as I would normally fill a ListBox.
Now I am not able to get the check boxes to do what I want unless I use an if function to change their state based on the true or false, and then I have tried to change the value by clicking the checkbox and updat the sheet, but can't find anywhere like a full sample code for me to adjust other then a few lines here and there.
I am really not an expert, so I would need some help in adding 2 dropdown lists and 2 check boxes to my iGrid that will update my sheet.

I have browsed here a few times for a few hours, but can't find a piece of code that I would be able to implement.

Hope anyone can help.

Thank you all in advance

Perhaps, it would be better if you sent us some code or published it here for better understanding of your problems. You have troubles with check box cells in iGrid, but it's not clear what exactly from your description.

As for combo box cells, you can find a good explanation how to use them in the help file. See Programmer's Guide\Programming Basics\Editing Cells\Using Combo Box Cells topic. Below is a typical code used to create items in a combo list:

The following sample shows how to use the defined combo list in one cell:

If you need to use the same combo list in all cells of a column, use the column default cell returned by the AddCol method:

Feel free to ask us more.

Hello, sorry for this late reply.

Am i mistaken or is the code that you send me for a combo box and not for a Check box?

below is how I am filling the data into the iGrid, however column 6,8,10,17 and 18 now display a True or False value which I would like to have displayed as a check box .
Then I have column 3,5 and 7 as a Combobox which I would like to populate with a named Range.
The code below fills it up without any issues and when I change the data the sheets is updated as well, so that is not the issue.
It is only the check boxes that I can't seem to get going and I can't get the Combobox filled with the named range.


Private Sub UserForm_Initialize()

Dim i As Integer
Dim LastRow As Long

LastRow = ShIngredientsData.Cells(ShIngredientsData.Rows.Count, "A").End(xlUp).Row - 1

iGrid1.ColCount = 28
iGrid1.RowCount = LastRow




For i = 1 To LastRow

iGrid1.CellValue(i, 1) = ShIngredientsData.Range("A" & i + 1).Value
iGrid1.CellValue(i, 2) = ShIngredientsData.Range("B" & i + 1).Value
iGrid1.CellValue(i, 3) = ShIngredientsData.Range("C" & i + 1).Value
iGrid1.CellValue(i, 4) = ShIngredientsData.Range("D" & i + 1).Value
iGrid1.CellValue(i, 5) = ShIngredientsData.Range("E" & i + 1).Value
iGrid1.CellValue(i, 6) = ShIngredientsData.Range("F" & i + 1).Value
iGrid1.CellValue(i, 7) = ShIngredientsData.Range("G" & i + 1).Value
iGrid1.CellValue(i, 8 ) = ShIngredientsData.Range("H" & i + 1).Value
iGrid1.CellValue(i, 9) = ShIngredientsData.Range("I" & i + 1).Value
iGrid1.CellValue(i, 10) = ShIngredientsData.Range("J" & i + 1).Value
iGrid1.CellValue(i, 11) = ShIngredientsData.Range("K" & i + 1).Value
iGrid1.CellValue(i, 12) = ShIngredientsData.Range("L" & i + 1).Value
iGrid1.CellValue(i, 13) = ShIngredientsData.Range("M" & i + 1).Value
iGrid1.CellValue(i, 14) = ShIngredientsData.Range("N" & i + 1).Value
iGrid1.CellValue(i, 15) = ShIngredientsData.Range("O" & i + 1).Value
iGrid1.CellValue(i, 16) = ShIngredientsData.Range("P" & i + 1).Value
iGrid1.CellValue(i, 17) = ShIngredientsData.Range("Q" & i + 1).Value
iGrid1.CellValue(i, 18) = ShIngredientsData.Range("R" & i + 1).Value
iGrid1.CellValue(i, 19) = ShIngredientsData.Range("S" & i + 1).Value
iGrid1.CellValue(i, 20) = ShIngredientsData.Range("T" & i + 1).Value
iGrid1.CellValue(i, 21) = ShIngredientsData.Range("U" & i + 1).Value
iGrid1.CellValue(i, 22) = ShIngredientsData.Range("V" & i + 1).Value
iGrid1.CellValue(i, 23) = ShIngredientsData.Range("W" & i + 1).Value
iGrid1.CellValue(i, 24) = ShIngredientsData.Range("X" & i + 1).Value
iGrid1.CellValue(i, 25) = ShIngredientsData.Range("Y" & i + 1).Value
iGrid1.CellValue(i, 26) = ShIngredientsData.Range("Z" & i + 1).Value
iGrid1.CellValue(i, 27) = ShIngredientsData.Range("AA" & i + 1).Value


Next i

End Sub

If you want to turn cells into check box cells by setting individual cell properties, it is done like this:



Note that you set not the cell value but the CellCheckState properti to control the state of the check box.

All this is described in the iGrid help file. See Programmer's Guide\Programming Basics\Editing Cells\Adding Check Boxes to Cells.

As for your problem with combo box cells, it's not clear how you want to use a named range to populate them. Can you enumerate the cells in the named range and add items one-by-one with the ComboObject.AddItem method? We need more info about your problem to help you.

Thank you Igor

I have now managed to load the grid with the checkboxes and set them by doing an if statement

iGrid1.CellCheckVisible(i, 6) = True
iGrid1.CellValue(i, 6) = ""

If ShIngredientsData.Range("F" & i + 1).Value = True Then
iGrid1.CellCheckState(i, 6) = igCheckStateChecked
ElseIf ShIngredientsData.Range("F" & i + 1).Value = False Then
iGrid1.CellCheckState(i, 6) = igCheckStateUnchecked
End If

Not sure if this is the best way but this is the only thing I could come up with
I have been going over the help files already a hundred times, but I think my knowledge and skills are not good enough as for me it does not tell me how to implement for example the BeforeCellCheckChange or AfterCellCheckChange or CellCheckState as all mentioned in the help files
Do I need to make it a sub routine, do I place it anywhere within a sub routine, I have no idea
So I want to be able to change ShIngredientsData.Range("F" & i + 1).Value in to true or false if the check box is changed as this is a true or false field in the sheet.

Right now the text fields are updated by the following

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(lRow + 1, lCol).Value = vNewValue

End Sub

And this works fine.
As soon as I change a field, it gets updated on the sheet.
Now I would like the checkbox also do the same in Column 6. Hope you can help me with this.

Thanks again

Hello Igor

I just figured it out I think

Private Sub iGrid1_BeforeCellCheckChange(ByVal lRow As Long, ByVal lCol As Long, ByRef eNewCheckState As ECellCheckState, ByRef bCancel As Boolean)


If eNewCheckState = igCheckStateChecked Then
Cells(lRow + 1, lCol).Value = True
ElseIf eNewCheckState = igCheckStateUnchecked Then
Cells(lRow + 1, lCol).Value = False
End If


End Sub

This is now doing my Check box update

I know it is working, but could you tell me if this is the best way?

Hello Igor

I have been looking at the Combobox, but I have the following issue

I have a list with over 1000 ingredients.
Now I can loop thru them and have the combobox filled up, but I have named Range that gives me all the data

=OFFSET(shIngredientsData!$B$2,,,COUNTA(shIngredientsData!$B:$B)-1,1)

In a normal combobox on a userform I would set this to the rowSource, is there something similar I can do in iGrid?

Checkboxes:
Your solution is right, though I would use the AfterCellCheckChange event. AfterCellCheckChange is raised AFTER the check box state has been changed, whichis generally what we need. But in your case, there is almost no difference.

Comboboxes:
There is no method to populate them automatically from an Excel range of cells. Only manually one-by-one or from and ADO/DAO recordset with the FilLFromRS() method, but that's another story.

Thank you for the reply

I have now looped the values like this and it is working like a charm

Dim r As Integer
Dim LastRowU As Long

LastRowU = shUnit.Cells(shUnit.Rows.Count, "A").End(xlUp).Row - 1

With iGrid1.Combos.Add("Units")

For r = 2 To LastRowU

.AddItem (shUnit.Range("B" & r).Value)

Next r

End With

This is a small list so it works, will try later with the longer list

I am posting this in case someone else is looking for solutions 🙂

Hello Igor

Is there another way that you can think of that could replace the combobox when you have over 5000 items to load?
I now load the combobox with a loop

With iGrid1.Combos.Add("TestIngr")

For tr = 1 To LastRowTr

.AddItem (shTestIngredients.Range("A" & tr).Value)

Next tr



End With

But as there are over 5000 it takes a little long to load.
What other options would you suggest to use in iGrid?

Generally combo lists are used to work with not such a big number of items. Do you think it's is convenient for the user to find the required item in a 5000 item list? Or do you use a combo list for another purpose - say, to look up the text determined by an id?

As I wrote, there are just 2 ways add items to a combo list - one-by-one using the AddItem method or with FillFromRS. Obviously, FillFromRS can't be use din your case, so you can use only AddItem.

As for your loop, I would move the access to the range object outside of the loop. Why? Because every call to the Range property creates a new instance of this object. What about the following modification of your code?