Microsoft KB Archive/132355

{|
 * width="100%"|

Using MS Excel 5.0 Dialog Boxes MS Excel 4.0 Macro Language

 * }

Q132355

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for the Macintosh, versions 5.0, 5.0a
 * Microsoft Excel for Windows NT, version 5.0

-

SUMMARY
Microsoft Excel version 5.0 includes several enhancements to the Microsoft Excel version 4.0 macro language that enable users to send and retrieve information from various dialog sheet controls in Microsoft Excel 5.0. The following information includes examples of tasks that you may want to perform using this functionality.

NOTE: In each of the following examples, the dialog sheet must be the active sheet prior to running each macro.

EDIT BOXES
To set the value of an edit box:

This examples sets the value of an edit box to "MyValue" and then displays the dialog box. The quotation marks are not necessary for numeric values.

  =SELECT("Edit Box 1") =TEXT.BOX("MyValue") =SHOW.DIALOG("Dialog1") =RETURN To obtain the value of an edit box:

  =SELECT("Edit Box 1") Answer=GET.OBJECT(12) =ALERT(Answer) =RETURN

CHECK BOXES
To obtain the value from three different check boxes:

Returns 0 if the check box is not checked, 1 if it is checked, and 2 if it is mixed.

  =FOR("counter",1,3) =Answer=GET.OBJECT(62,"Check Box " &counter) =ALERT(Answer) =NEXT =RETURN

OPTION BUTTONS
To obtain the value of three different option buttons:

Returns 0 if the option button is not selected and 1 if it is selected.

  =FOR("counter",1,3) Answer=GET.OBJECT(62,"Option Button " &counter) =ALERT(Answer) =NEXT =RETURN

LIST BOXES
To obtain the selected value of a list box:

This macro will return the #VALUE! error if nothing is selected.

  Answer=GET.OBJECT(72,"List Box 1",1,100,GET.OBJECT(62,"List Box 1")) =ALERT(Answer) =RETURN To set the selected item in a list box:

This macro sets the selected value to the third item of the list.

  =SELECT("List Box 1") =SELECT.LIST.ITEM(3) =SHOW.DIALOG("Dialog1") =RETURN

DROP-DOWN LIST BOXES
To obtain the selected value of a drop-down list box:

  Answer=GET.OBJECT(72,"Drop Down 1",1,100,GET.OBJECT(62,"Drop Down 1")) =ALERT(Answer) =RETURN To set the selected item of a drop-down list box:

This macro will not take effect until the drop-down control is selected at least once. This macro sets the selected value to the third item in the drop-down list.

  =SELECT("Drop Down 1") =SELECT.LIST.ITEM(3) =SHOW.DIALOG("Dialog1") =RETURN

COMBINATION LIST-EDIT BOX
To obtain the selected value of a combination list-edit box:

  Answer=GET.OBJECT(72,"ComboList1",1,100,GET.OBJECT(62,"ComboList1")) =ALERT(Answer) =RETURN To set the selected item of a combination list-edit box:

This macro sets the selected value to the third item of the list.

  =SELECT("ComboList1") =SELECT.LIST.ITEM(3) =SHOW.DIALOG("Dialog1") =RETURN To obtain the value in the edit box portion of a combination list-edit:

  =SELECT("ComboEditBox1") Answer=GET.OBJECT(12) =ALERT(Answer) =RETURN

COMBINATION DROP-DOWN EDIT BOXES
To obtain the selected value of a combination drop-down edit box:

 Ans=GET.OBJECT(72,"ComboDropDown1",1,100,GET.OBJECT(62,"ComboDropDown1")) =ALERT(Ans) =RETURN To set the selected item of a combination drop-down edit box:

This macro sets the selected value to the third item of the drop-down box.

  =SELECT("ComboDropDown1") =SELECT.LIST.ITEM(3) =SHOW.DIALOG("Dialog1") =RETURN

SCROLL BARS
To return a scroll bar value to an edit box:

Assign the control to the following macro by clicking the dialog box control with the right mouse button and choosing Assign Macro from the shortcut menu.

  Answer=GET.OBJECT(62,"Scroll Bar 1") =SELECT("Edit Box") =TEXT.BOX(Answer) =RETURN To obtain the scroll bar step increment value:

Assign the control to the following macro by clicking the dialog control with the right mouse button and selecting Assign Macro from the shortcut menu.

  Answer=GET.OBJECT(65,"Scroll Bar 1") =ALERT(Answer) =RETURN

SPINNERS
To return the value of a spinner:

Assign the control to the following macro by clicking the dialog control with the right mouse button and choosing Assign Macro from the shortcut menu.

  Answer=GET.OBJECT(62,"Spinner 1") =ALERT(Answer) =RETURN Additional query words: Dialogs

Keywords : _IK kbmacro kbprogramming

Issue type : kbhowto

Technology : kbHWMAC kbOSMAC kbExcelSearch kbExcel500 kbExcelMacsearch kbExcel500Mac kbExcel500aMac kbExcel500c kbExcel500NT