Using MS Excel 5.0 Dialog Boxes MS Excel 4.0 Macro Language

ID: 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.

MORE INFORMATION

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          : kbcode kbmacro kbprg PgmOthr 
Version           : WINDOWS:5.0,5.0c; MACINTOSH:5.0,5.0a
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: December 2, 1997