Worksheet Table in User Form

[mc84excel]

I am a heavy use of arrays. But reading the contents in the Locals window is not practical for anything but micro arrays. And dumping it to a WS range is a nuisance - especially if I'm in the middle of testing a Modal form.

It's not difficult to display an array on a UserForm but I want to be able to:

1. Sort any column in the ListBox/ComboBox
2. Apply (and remove) basic filtering of columns

In other words - I'm visualizing the equivalent of a Worksheet Table running inside a form.

Has anyone written anything like this?

[mc84excel]

No one?

I guess it would be possible to add a 2nd form which would act as the column filter options.

[MarvinP]

Hi mc84excel.

It is a little tough to understand the question without an example or some attachment. You are asking if you can do the work of a table in a VBA array. The answer is YES. It would simply take a lot of code and work slower than using a table in the first place.

Pivot Tables do a lot of array stuff, like sorting and filtering. Why do you want to put it on a Userform instead of leaving it in a worksheet?

Look at
http://www.cpearson.com/excel/VBAArrays.htm
to see lots of array VBA functions that may get to what you are asking.

[mc84excel]

Hi Marvin,

I don't have an example yet as I'm checking to see if any one has created something like this before I jump in and reinvent the wheel

Yes, I want to display an arrays results in a userform. The difficult part is that I want it to have similar controls to what a Table has in Excel (Sorting and Filtering options).

I want this because I'm a heavy user of arrays. I think I'd find it useful when I'm writing code to call a userform that would display the arrays contents so I can check what values the array is currently holding. (It's to assist code writing, I don't need to return any results from the userform). Up to now I've been dumping the array to a new WB and I just find it fiddly. (Particularly if you have screenupdating = False or you are writing a Modal form)

[Norie]

Spreadsheet Control?

[mc84excel]

Spreadsheet Control?

I mean the Filter thingies that appear at the top of each column in a worksheet Table.

[romperstomper]

Norie is referring to the spreadsheet control from the office web components library (deprecated from XL2007 on but still downloadable) - you can embed it on a userform.

[Kyle123]

A listview with some filtering comboboxes? Doesn't seem like a huge amount of work - though Norie's suggestion will be much easier, given your appreciation of doing things the hard way, I suspect that will mean that you want to roll your own

[mc84excel]

A listview with some filtering comboboxes? Doesn't seem like a huge amount of work - though Norie's suggestion will be much easier, given your appreciation of doing things the hard way, I suspect that will mean that you want to roll your own

Ha ha I do things the hard way? I know others here who would agree with you but I don't believe that I do think that way. My philosophy is - If something is already out there and it does everything you want it to then why reinvent the wheel? Wastes time and effort. However I AM prepared to start something 'hard' if it doesn't exist and I want it (e.g. my infamous macro un-recorder)

P.S. A listview with filtering comboboxes sounds huge to me. I think I will look into Nories suggestion.

[mc84excel]

Norie is referring to the spreadsheet control from the office web components library (deprecated from XL2007 on but still downloadable) - you can embed it on a userform.

Ah. Thank you for explaining that Rory. I didn't know what Norie was referring to. (I have never heard of 'spreadsheet control' before).


One problem - how do I add 'Spreadsheet Control' to a form? I've looked under 'Additional Controls' and I can't see an Office Web Components Library. I've tried googling how to add SpreadSheet Control and couldn't find anything useful. Could some kind forum user please explain how to do this?

[Norie]

What exactly did you look for under Additional Controls?

I've got the spreadsheet control listed as Microsoft Office Spreadsheet 11.0.

[mc84excel]

I looked for 'Office Web Components Library'

I'm looking under 'Additional Controls' and there is no 'Microsoft Office Spreadsheet 11.0'. It goes straight from 'Microsoft ListView Control, version 6.0' to 'Microsoft Outlook Body Control'.

nvm I found it. For anyone reading this who needs it - download this installer from MS: http://www.microsoft.com/en-us/downl....aspx?id=22276

[mc84excel]

This 'Spreadsheet Control' is all new to me. I have it embedded on a UserForm but I'm not certain as to how to proceed next. e.g. How do I programatically populate this control?

Here's what I am trying to achieve:

Say I am testing a procedure that involves a 1D or 2D variant array. While testing, I want to browse/search the array data at certain points of the procedure. I would import the finished 'Array Viewer Form' into the workbook and pass the array to this form at these points. Upon closing the form, the procedure would continue. When I am satisfied with the procedure, I remove the form and the associated code.

[Norie]

Here's some a very, very simple example of how to populate a spreadsheet control and turn on filtering.

Please Login or Register  to view this content.

[mc84excel]

Thanks Norie. That helps me grasp it. +1

[mc84excel]

The control seems to be far more limited than a normal worksheet.

I don't suppose it would be possible to hide the excess columns?(If not, I guess I could just resize the control at runtime and disable the horizontal scroll bar?)

[Kyle123]

Why bother if this is just for debugging?

[romperstomper]

It's simple enough to hide the columns, it's just like a normal sheet but with more columns:

Please Login or Register  to view this content.

[mc84excel]

Why bother if this is just for debugging?

Well when you put it like that, it doesn't really matter I guess. (I'd just like it to look less messy when I use it )

[mc84excel]

Thanks Rory +1