VBA DataGrid User Form

[ChloeRadshaw]

Hi All,

I need to be able to show data in a userform which is *not* coming back from a database.

Does anyone know the best way to do this? What form object do I need to create?

I looekd at MSFlexGrid but it is not supported in VBA

Is there anything which works out of the box which allows you to specify the number of columns in a row and then let you populate the form?

[GTO]

Greetings ChloeRadshaw,

When you say "database", are you referring to an Excel worksheet?
Not sure about MSFlexGrid, but you can use RowSource to grab a range. Not exactly ideal under many situations however.

Really, if you could attach an example workbook with the userform and goal, a better answer would be much easier to provide.

Hope this helps,

Mark

[Kenneth Hobs]

Is the data coming from an Excel range?

You can use a Listbox or ComboBox with multiple columns. You can add each item individually or use RowSource or use List for addition by an array.

I had trouble using the example in the help for the flexgrid control. I used the more simple example. Change the path to the NWind.mdb to test. We use something similar to query Excel data.

'http://pages.cpsc.ucalgary.ca/~saul/vb_examples/tutorial3/index.html
'Requires 2 controls from the toolbox: MSHFlexGrid and ADOdc
Private Sub UserForm_Initialize()
 ' Create a ConnectionString.
Dim strCn As String
'strCn = "Provider=MSDataShape.1;Data Source=e:\excel\ado\Nwind.mdb;" & _
"Connect Timeout=15;Data Provider=MSDASQL"
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\excel\ado\Nwind.mdb"


' Create a Shape command.
Dim strSh As String
'strSh = "SHAPE {SELECT * FROM `Customers`}  AS Customers " & _
"APPEND ({SELECT * FROM `Orders`}  AS Orders RELATE " & _
"CustomerID TO CustomerID) AS Orders"
strSh = "SELECT * FROM Orders"

' Assign the ConnectionString to an ADO Data Control's
' ConnectionString property, and the Shape command to the
' control's RecordSource property.
With Adodc1
   .ConnectionString = strCn
   .RecordSource = strSh
End With
' Set the HflexGrid control's DataSource property to the
' ADO Data control.
Set MSHFlexGrid1.DataSource = Adodc1

End Sub

Of course the Spreadsheet control is easy to use.
http://www.mrexcel.com/forum/showthread.php?t=74529
If you go this route, this link shows some other tips. http://excelkb.com/article.aspx?id=10190

[ChloeRadshaw]

I should have been clearer in my original post - The data is held in a variant array - It needs to be processed by me first.

If I want to use an array as a data source are there any controls that would help me do this?

Any downloadable (easy to use) ones?

Thanks

[Kenneth Hobs]

"You can use a Listbox or ComboBox with multiple columns. You can add each item individually or use RowSource or use List for addition by an array."

Example with one listbox and one command button control:

Private Sub UserForm_Initialize()
  Dim a() As Variant
  a() = [{"First", "Last"; "Kenneth", "Hobson"; "Jane", "Doe"}]
  
With ListBox1
    .ColumnCount = UBound(a, 2)
    .BoundColumn = 2
    .List = a()
    .ListIndex = 1  'Select 2nd row
  End With
End Sub

Private Sub CommandButton1_Click()
  MsgBox "You chose: " & ListBox1.Value
  Unload Me
End Sub

You can use the BoundColumn property to get the SelectIedtem if needed.

Of course, you can still use the flexgrid and spreadsheet controls but you would have to fill a sheet to show discontinuous data. Another option for the flexgrid method would be to export your data to an external file such as a csv file and use it as the datasource. It is faster than you might think.

[tecman]

To answer this question completely, yes, such a component exists. It's 10Tec iGrid ActiveX. It does exactly what you need and works similar to MSFlexGrid. You just define the number of rows and columns, and access its cells like in a 2-dimensional array to set their values. You can find code samples on their website (just google it). No database is required