User Forms

[ Home ] [ Access - Start Here ] [ Excel In Depth ] [ MS Office ] [ Visio Info ] [ Creating Web Pages ] [ Technical References ] [ Viruses ] [ Points of View ] [ Gadgets ] [ Just Links ] [ Monash ]

Post a message

Monitor page
for changes

by ChangeDetection

[ Add-Ins ] [ Macros ] [ User Forms ] [ Misc ]

What’s happened to Excel 5 Dialog Sheets?

Excel 5 Dialog Sheets and their Controls have been absorbed into the Excel ’97 Object Model and hidden (by default) in the Object Browser listing.

To display all objects, press F2 when you are in the VBE to see the Object Browser. Then right click on the Classes listbox and enable Show Hidden Members.

You can count all Excel 5 Dialog Sheets in the current workbook by

dialogsheets.count

To retrieve the text in an editbox in a dialogsheet, use

dialogsheets(1).editboxes(1).text

So, they have not disappeared, Microsoft still has legacy support for them, but prefers not to advertise the fact.

Why are there two Toolbars containing form controls?

When you see a list of Toolbars in Excel’s View, Toolbars, you will see the Forms Toolbar and the Controls Toolbox.

The Forms Toolbar holds the controls that complement Excel 5 Dialogsheets (legacy).

The Controls Toolbox holds new controls which complement the VBA UserForms (a.k.a Forms 2.0). VBA Userforms can additionally hold ActiveX controls.

On the other hand, items in the Controls Toolbox cannot be placed on Charts while items from the Forms Toolbar can.

What are the differences between Excel 5 Dialogsheets and Excel ’97 Userforms?

Excel 5 Dialogsheet	Excel ’97 Userform
Holds Controls from the Forms Toolbar	Holds Controls from the Control Toolbox
Stored in a Sheet in an Excel Workbook	A component of the VBA Project
After display and interaction with the user, the Dialogsheet and the Controls retain their value/text	After display and interaction with the user and then Unloaded, the values disappear. Use the values BEFORE you Unload the form
Displayed via Dialogsheets(1).show or retval=Dialogsheets(1).Show	Displayed via Userforms(1).Show or VBA.UserForms.Add(X).Show Also see XL97 How to Display a UserForm Whose Name Is in a Variable
Closed by clicking on an Ok or Cancel button (Dismiss or Cancel action). See XL97 No Trappable Events for Dialog Sheets and Macro Sheets	Closed on the click event of a button with the following code: Unload Me XL97 Preventing UserForm from Being Dismissed with Close Button
Microsoft has made available an Excel 97 Dialog Converter to convert Excel 5 Dialog Sheets to Userforms.	XL97 How to Use a UserForm for Entering Data

What are the differences between Controls on the Forms Toolbar and the Controls Toolbox Toolbar?

Forms Toolbar (e.g. Combo Box)	Controls Toolbox Toolbar (e.g. Combo Box)
Use Right Click, Format Control to get to the Properties Dialog.	Use Right Click, Properties to get to the Properties Dialog. When placed on a worksheet, the Properties Dialog has less Tabs.
Properties Dialog has Input Range and Cell Link entries which allow browsing to the destination cells.	Properties Dialog has RowSource and ControlSource entries. These entries do not feature browsing: (see XL97 Cannot Use Mouse to Populate RowSource or ListFillRange), you have to enter the values manually: See (XL97 How to Add Data to a ComboBox or a ListBox) An example of a RowSource entry is Sheet1!A1:A4 An example of a ControlSource entry is Sheet1!B1 You can explicitly specify the Workbook name in this syntax: [Book1]Sheet1!A1 An alternative way to fill the list is to use Cbo.additem "Victoria" etc… in the UserForm1_Initialize event or use Cbo.List = ArrayItems() Listboxes can display more than one column: XL97 Returning Values from ListBox Displaying Multiple Columns and XL97 How to Use the TextColumn Property You can drop down a combo box automatically: XL97 How to Display a ComboBox List When UserForm Is Displayed Note: XL97 List Box Doesn't Work Correctly When Workbook Is Hidden
You can assign a macro (Sub) to the Control	Double click on the Control and you will get to the Source Code Window. There will be event properties in the event names drop list e.g. Change, Click, Enter etc… When placed on the worksheet the Source Code Window is within the class module associated with the worksheet.
Names are typically "Check Box1". To access the control, you would use (full pathname): `Workbooks("myworkbook"). Worksheets("myworksheet"). Checkboxes("Check Box1"). value` To refer to a control by type, use `dialogsheets(index). editboxes(index)`	Names typically "CheckBox1". To access the control, you would use (full pathname): basMyworksheet.CheckBox1.value To refer to a control by type, use for each objCtl in Userform1.controls debug.print typename(objCtl) next or for each objCtrl in Userform1.Frame1.Controls Option Groups don't hold values, only Option Buttons do. You can put more than one set of Option Buttons on (so that you can get more than one set of values) by assigning different values to the .GroupName property or embedding them in different Frames. See XL97 Macro Examples Using OptionButton Controls on a UserForm. Note also: XL97 Problems Using TripleState Property for OptionButton Don't forget about ToggleButtons XL97 How To Create a Group of Mutually Exclusive ToggleButtons
	There are new controls like the TabStrip, see: XL97 TabStrip Information Missing from Value Property Help and Multipage Controls. There are also missing controls - see: XL97 No Combination List-Edit Control for UserForms. The RefEdit control is missing documentation - see: XL97 Using the RefEdit Control with a UserForm