Is this control available to Excel userforms?

[BigBas]

I'm creating a userform within which I'd like to use a control very similar to the Excel properties dialog box (see attached picture). Essentially, 1 column includes lables, and the next column takes a value. Is this type of control available to VBA developers? If so, what is it called?

[mikerickson]

Your Screen Shot looks like a combination of
one Label
one ComboBox
one MultiPage
multi-column ListBox (or maybe two single column)

There is no single control that acts like that, but you could put together different controls to make a userform that looks and acts like that. The tough part would be the right column of the ListBox, that sometimes has arrows drop when the user selects that column of that entry.

[BigBas]

Essentially, it is as you've mentioned a combination of the individual controls.

I've seen this type of a structure in a number of applications. Most only have the label column and the "textbox" column. I have difficulty believing that in each of these cases, the developer individually creates a label/text box for each entry. That is a ton of work and not very scalable.

Is it possible that this type of control exists in other programming languages?

[mikerickson]

Its possible, but as to the scalability, I've been running over how I would program that control (userform) and it would be easily scalable.

The main issue would be the right column. I would have a custom object with an .AddLine method. Controls created on the fly would work fine.
The control you posted is a sophisticated object, but quite reachable with Excel VBA.

[Kyle123]

Scabbily thrown together from something else I had lying around. Would need a bit of adapting for a combobox (and a bit of trating up), but it should be relatively straightforward

[BigBas]

Mikerickson: Do you have an example of how that would be created/scaled? Obviously, I don't expect you to create a complex form, but let's just assume lables/textboxes. How would that be done?

[BigBas]

@Kyle, interesting spreadsheet. I'm looking through it to see how it works

[Kyle123]

Tarted up a bit and added combobox functionality.

I originally made this for mc85Excel here (http://www.excelforum.com/excel-prog...91#post3314191), the control has a huge amount of scope and is easily adaptable once you've got to grip with classes, there is a huge amount going on in there though and some concepts that you don't see an awful lot so if you want to know how it works and have any questions, let me know

[Kyle123]

Added highlight on row select with mouse (was too much work to make it work with key press) and dictionaried it up for easy direct access to key/value pairs. Since this might be useful to other people, I'll clean up the code in the attached and stick it in the tips forum

[BigBas]

Definitely some interesting stuff. I'm at work so can't really dig too deeply into it, but I've definitely marked this post for future viewing.

Also, REP added!

[mikerickson]

Mikerickson: Do you have an example of how that would be created/scaled? Obviously, I don't expect you to create a complex form, but let's just assume lables/textboxes. How would that be done?

My plan would be to put a Frame in the userform.
This Frame would be the core of the custom object clsDualListBox
The .AddLine method of the custom object would create a TextBox (for the name of the property) and a ComboBox (for the value) and put it in a Collection.
Aside from the font, width etc. bells and whistles, one problem that I see is that the Value might be a string, but it also might be an Object (e.g. Font). I'm thinking of using another custom object clsRowLine (the textbox + combobox) to handle the machinery of declaring what kind of values are acceptable.

[Kyle123]

There's an updated version of my attachment here: http://www.excelforum.com/tips-and-t...ddly-bits.html

[mikerickson]

Here's my effort.
When I look at the Property window, I see rows of paired boxes. One box contains the name of the property. The other is for user entry. The user entry box is either
a TextBox (e.g. Caption property)
a dropdown (e.g. ShowDropButtonWhen property
a button (showing elipsis) that invokes another dialog box (e.g. Font property).

In the attached, I created a custom control, clsHeaderedList, which contains a list of other custom controls, clsHeaderedRow.
A clsHeaderedList is basicaly a Frame with stuff inside.
A clsHeaderedRow is, at the core, a Header and a Value plus machinery to allow the user to edit the Value

A clsHeaderedList has methods and properties:

AddRow - adds a clsHeaderedRow to the list, the Name argument is required
Clear
ListCount - (read only) the number of rows in the control
RowsList - (read only) a collection of the clsHeaderedRow s in the control. It will accept either Names or index numbers as its arguments.
Parent - the Frame control that holds/is the control

the other properties are (at this time) for internal use only.

A clsHeaderedRow is a HeaderBox and a UserEntryBox.

The properties and methods of clsHeaderedRow are

Name - (read only) the unique name given to the row. Once set as the argument for the RowsList property of the parent clsHeaderedList, it cannot be changed thereafter.
Header - (read/write) the text displayed in the clsHeadederRow 's HeaderBox
Value - (read/write) the Value (contained in the UserEntryBox)
Parent - the clsHeaderedList that contains the clsHeaderedRow. Should be treated as read only.

The UserEntryBox has three modes,
textbox mode, the default entry mode
dropdown mode, select from list
Elipsis mode calls a dialog box when clicked. Which dialog is called is controled by the ElipsisClick event. (see below)

To change to dropdown mode, the developer uses the ValidationList property to set the list of acceptable values for that row.

The HasElipsis property is a read/write boolean value indicating whether the control is in elipsis mode.
The ElipsisTag property is a read/write string, used to pass information back to the developer when the control's button is clicked. HasElipsis is True iff ElipsisTag <> vbNullString

As it is set up, the developer writes all of their code in the userform module.

The developer creates a userform with a Frame to act as the Parent of the clsHeaderedList
Instansizing a clsHeaderedList is done with code like

Please Login or Register  to view this content.

This code (in the userform code module) creates the HeaderedRows with headers from column A.

Please Login or Register  to view this content.

Then, the non-textbox mode rows have their input mode set

Please Login or Register  to view this content.

EVENTS:
A clsHeaderedList has two events:
Change(RowChanged As clsHeaderedRow) - which fires whenever any UserInputBox is changed. The RowChanged argument is the row that was changed.

ElipsisClicked(RowClicked As clsHeaderedRow) - which fires whenever a row that is is elipsis mode has its button clicked. Note how the ElipsisTag is used to tell the userform which dialog box to show.

Please Login or Register  to view this content.

TO DO:
- Width properties have been ignored. A ColumnWidths property (for clsHeaderedList) would be useful. Accomidating the possibility of a scroll bar is one of the challanges in writting these properties.
- formatting properties (e.g. Backcolor, Font, TextAlign etc) have not yet been addressed
- masked values in dropdown mode. In the property window, the user sees "0-fmListStylePlain" and "1-fmListStyleOption" when the underlying values are actually 0 and 1. Currently clsHeaderedList does not have that capability. Adding a Text property to clsHeaderedRow would require making the UserEntryBox a two column combobox with hidden column.
- RemoveRow method is needed for clsHeaderedList. Similarly the .AddRow could have a indexing argument.

[Kyle123]

Morning Mike,

I quite like that, an improvement, if I may, would be to add a dispose method to your clsHeaderedList, you've currently got a circular reference which isn't resolved:

Please Login or Register  to view this content.

Then called explicitly from the userform:

Please Login or Register  to view this content.

[mikerickson]

One can not instansize a custom object from inside it. Neither can one de-instansize a clsHeaderedList from within its class module.

Adding this method to clsHeaderedList's class module does almost as good as de-instansizing.

Please Login or Register  to view this content.

and called with

Please Login or Register  to view this content.

Here is an updated version, clsHeaderedList has two new properties, ColumnWidths and Width.

[Kyle123]

That wasn't the intention, it was merely to run the code in the sub to release the child references - this didn't occur in your original code. You are also still not resolving all your circular references, the following would suffice in your clear routine:

Please Login or Register  to view this content.

You'd also need to call it for any method of closing the userform