Excel VBA Grid Control

Edited by

This brief article is dedicated to the usage of our ActiveX grid control, iGrid, as a Microsoft Excel VBA grid control. iGrid is used in this VB automation environment as a UserForm control, and thus all the words below are also applicable to MS Word which also can use VBA UserForms.

Using iGrid as an Excel VBA grid control

Well, let's start from the Microsoft Visual Basic for Applications environment for Excel/Word. As you can see from the picture below, you can add iGrid OCX as an Excel VBA grid control to the Toolbox and drop its instance on a UserForm:

Adding iGrid as Excel VBA grid control to UserForms Toolbox

The VBA property grid can be used to change the properties of our OCX grid control like you can do that for any intrinsic VBA UserForms control. As for doing this in code, you won't face any problems with using iGrid's properties, methods and events in VBA code editor - iGrid was built and compiled in the classic VB environment (VB6) and is totally based on the VB/VBA runtime library.

When we launch that VBA form with our grid control from the previous screenshot, we'll see the following picture which will help us to highlight some important features of iGrid when it is used as an Excel VBA grid:

10Tec Excel VBA grid in action

The main benefits of the iGrid usage in MS Word/Excel VBA are the followings:

  • iGrid's cells can store values of any type as they are Variant. This means you can have values of different data types even within the same column.
  • iGrid fully supports displaying and editing of Unicode texts in all modern versions of MS Windows.
  • You can use numerous cell formatting options (like background and foreground color, custom fonts, word wrapping and any vertical/horizontal alignment of cell contents).
  • If a cell's text is truncated, you can see the full cell text in the built-in tooltip like on the picture above.
  • iGrid allows you to edit its cells using not only simple text editor, but also drop-down combo lists and check box controls.
  • The grid contents can be sorted (multi-column sort is available) and grouped using its visual interface.
  • One of the grid columns can display a TreeView so you will have a multi-column VBA tree grid.

As for the iGrid OCX itself, it does not depend on any external libraries. If you need to redistribute this control with your end-user Excel VBA solution (template, add-in, etc), you just need to copy the OCX file to the client's computer and register it in the Windows registry like any other ActiveX control.

Alternative free solutions and the final words

If you search the Internet using a query like excel vba grid control, most likely, the first search engine result page will contain links to articles in which authors write how to use the MS FlexGrid control supplied with Visual Basic 5/6 as a VBA grid control (see, for instance, this link). You can try similar search queries like excel userform grid control or activex vba grid control, but in the vast majority of cases the first 10-20 search results will point to the use of MSFlexGrid or ListView as VBA grid controls.

Yes, the ListView control from the MSCOMCTL.OCX common controls pack can be also used as a Word/Excel VBA grid if it works in report mode. But one of the most significant technical problems of both MSFlexGrid and ListView is that they do not allow you to edit its cells. If you need cell editing in those controls, you will need to use an external text box and a lot of additional code to maintain this construction. 10Tec grid does not require all this as its cell editors are built-in features.

Another drawback of these free UserForms VBA grids is that they were developed before 2000, and they are no longer supported. As consequence, ListView and FlexGrid have the outdated unchanged 3D look. iGrid is still supported, and from this point of view, it uses all the visual benefits of modern versions of Windows like visual styles. By default, its column headers and scroll bars have the same look-and-feel like you can see in other native Windows applications (Windows Explorer, etc) - which is nice for the end-users of your Excel VBA solutions.

The list of other distinctive features of iGrid if we compare it to FlexGrid or ListView can be found in dedicated articles in this section (one of them is Editable ListView Replacement, iGrid). If you became intrigued by the usage of iGrid ActiveX as VBA Excel grid control, you can continue your acquaintance with the control in the Control Tour using the link below.

Find out more about this VBA grid control in the Control Tour »