What GRID components are available for Excel VBA?

ou81aswell
ou81aswell used Ask the Experts™
on
I would like to know if there is a simple MSFlexGrid type of control that I can use on Excel VBA forms?

There doesn't seem to be a simple way to use the MSFlexGrid OCX that comes with VB6. I also have Component One's VSFlexGrid (ActiveX and .Net) but I'm looking for something much more lightweight and unbound (my code populates the grid itself, no ADO support needed).

Additionally, will these grids work with VBA forms in Excel 2010 x64?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
The best thing to use is just an Excel ocx.  If you right click on controls box (in form design) and then go down till you find Microsoft Office spreadsheet 11/12 (depends on what you have installed).  Select that and it will now be a control you can pick from the controls toolbox.  Add it to your form - works great as a grid.

After it's on your form, you can view its properties and adjust to your liking - take away menu bars, tabs, etc...and make it out to be a grid.  

Now you have a full powered excel grid to use on your form.  

See image.
excelonform.jpg

Author

Commented:
Thanks. Exactly what I was looking for.

Author

Commented:
This does not work for Excel 2007. The spreadsheet control is not available with Excel 2007.
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Author

Commented:
MS used to ship Office Web Components, (OWC) a collection of COM controls for using spreadsheets, charts, and databases on userforms in VBA, with Excel 2003.

It is deprecated with Office 2007 and it's not clear what the recommended replacement will be but it is still  available for download from Microsoft and works with Excel 2007. The downside is that this has to be installed on your users' machines separately as it is no longer installed by the Office setup program.

The MS download info is below.

Office 2003 Add-in: Office Web Components

http://www.microsoft.com/downloads/details.aspx?FamilyID=7287252c-402e-4f72-97a5-e0fd290d4b76

There's also a service pack for this add-in for Excel 2007 users.

Microsoft Office 2003 Web Components Service Pack 1 (SP1) for the 2007 Microsoft Office System

http://www.microsoft.com/downloads/details.aspx?FamilyId=C815DFFA-D5F3-4B71-BF46-13721BD44682

Commented:
The component you are searching for is 10Tec iGrid ActiveX. It's an unbound datagrid that works similar to MSFlexGrid: you just define the number of rows and columns, and access its cells like items in a 2-dimensional array:

iGrid1.ColCount = 3
iGrid1.RowCount = 2

iGrid1.CellValue(1, 1) = "Text value"
iGrid1.CellValue(2, 1) = 123
iGrid1.CellValue(3, 1) = #9/6/2009#

iGrid1.CellValue(2, 3) = "Some text"
iGrid1.CellForeColor(2, 3) = RGB(255, 0, 0)

Open in new window


You can find more code samples here:

http://10tec.com/activex-grid/code-examples-1.aspx

Perhaps, the only drawback is that it is a 32-bit control and thus it can't be used in the 64-bit editions of MS Office.

Author

Commented:
That would have fit the bill nicely.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial