VBA Control - Table/Grid

[scriptman]

I need to display information read from a database in a table in a VBA userform (MS Excel). I am aware of MSFlexGrid, and have tried to use it, but I have ran into a brickwall due to its well documented limitations. The following are the features I need from the table/grid control:

-row-based event detection (mouseover, mouse clicks, etc)
-ability to choose between, on a per-column basis:
Automatic resizing of column-widths if text in a row is too long
OR
Text wrapping if string in a row is too long for a defined column-width
-Column sort
-Automatic vertical scrollbar if number of rows is large
-Ability to have hidden columns
-Freeware!

Are there any controls that exist out there that have all of these abilities? Do you have any other solutions for displaying data in a grid in a VBA userform? Thanks for your help.

[Hack]

Excel VBA question moved to Office Development

[RobDog888]

One neat trick is to use an "Microsoft Office Spreadsheet xx.0" control.

Gives you everythiing that the Excel Sheet does but placed on a UserForm.

[malik641]

Be careful with the Spreadsheet control, it doesn't support ALL excel's objects, methods, collections, and properties.

For example, today I was helping someone with this object. They wanted to use AutoFilter and automatically filter a specific item, but the Spreadsheet control doesn't support "Criteria1, Field, Criteria2, etc" for Autofilter. I did manage to find a workaround, after some digging.

Incase you have a question about that, check out:

http://www.vbaexpress.com/forum/showthread.php?t=9940

And I also couldn't set a Range variable to the range on the Spreadsheet control, which was weird...I kept getting a type mismatch error.

[RobDog888]

Just dim your Range variable as an Object.

Option Explicit
 
Private Sub UserForm_Click()
    Dim oRange As Object
    Set oRange = Spreadsheet1.Range(Spreadsheet1.Cells(1, 1), Spreadsheet1.Cells(1, 2))
    MsgBox oRange.Address '"$A$1:$B$1"
End Sub
 
Private Sub UserForm_Initialize()
    Spreadsheet1.Cells(1, 1) = "Meow!"
    Spreadsheet1.Cells(1, 2) = "Meow!"
End Sub

[malik641]

Just dim your Range variable as an Object.

Option Explicit
 
Private Sub UserForm_Click()
    Dim oRange As Object
    Set oRange = Spreadsheet1.Range(Spreadsheet1.Cells(1, 1), Spreadsheet1.Cells(1, 2))
    MsgBox oRange.Address '"$A$1:$B$1"
End Sub
 
Private Sub UserForm_Initialize()
    Spreadsheet1.Cells(1, 1) = "Meow!"
    Spreadsheet1.Cells(1, 2) = "Meow!"
End Sub

Hmmm...so is it were as if I'm connecting to excel using late binding?

[RobDog888]

It appears to be the case but I really think it may be that the control uses a slightly different "Range" object with the same or similar props, methods, and functions etc. So the types may not be 100% equilivalent and cause the early binding type mismatch error.

I just use early binding to get my code in place and take advantage of the intellisense and then switch it to late binding and Im good to go.

[malik641]

It appears to be the case but I really think it may be that the control uses a slightly different "Range" object with the same or similar props, methods, and functions etc. So the types may not be 100% equilivalent and cause the early binding type mismatch error..

I see...do you think it has anything to do with the fact that it's the Web Components object model and not Excel?

I just use early binding to get my code in place and take advantage of the intellisense and then switch it to late binding and Im good to go.

Even if your code's written in VBA? (I can understand automating, say, access from excel...but I meant automating excel from within excel).

[RobDog888]

It could be as the object type may be named differently or just different enough to not match.


Yes, I meant Excel VBA inside of Excel VBA for just the code concerning the UserForm with the web control on it.

[malik641]

Yes, I meant Excel VBA inside of Excel VBA for just the code concerning the UserForm with the web control on it.

....for the web components you still get the intellisense even without the reference. The intellisense treats it (Spreadsheet object) as if it's a worksheet object...

[RobDog888]

Yes, but if you declare an object variable of Range and then try to set the range to a range on the web control you get the type mismatch erro like we been over. SoI was meaning that when writting the code that is directly related to the web control I do this but never removing any references as thats not needed.

Dim oRange As [b]Excel.Range[/b]
    Set oRange = Spreadsheet1.Range(Spreadsheet1.Cells(1, 1), Spreadsheet1.Cells(1, 2))
    MsgBox oRange.Address '"$A$1:$B$1"

Dim oRange As Object
Set oRange = Spreadsheet1.Range(Spreadsheet1.Cells(1, 1), Spreadsheet1.Cells(1, 2))
    MsgBox oRange.[b]Address[/b] '"$A$1:$B$1"

Where .Address is not displayed when using late binding to get around the type error. So I use this first and then switch it over.

[malik641]

Ok, I gotcha. Thanks RobDog888