XL98: MS Excel 5.0 Dialog Sheet Closes When Control Is Clicked

ID: Q182870

The information in this article applies to:

• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

After you click a control on a dialog box in Microsoft Excel 98, the dialog box closes.

CAUSE

This behavior may occur when the following conditions are true:

• You create an edit box on a dialog sheet, and you format the edit box to validate a reference.

      -and-
  

• You assign a macro to one of the following controls on the dialog sheet.

        OptionButton
        CommandButton
        Scrollbar
        Checkbox
        ComboBox
  

WORKAROUND

In Microsoft Excel 98, UserForms replace dialog sheets for use as a user interface. You can find UserForms in the Visual Basic Editor, which is the Visual Basic for Applications editing interface in Microsoft Excel 98. In a UserForm, the RefEdit control is the equivalent of an edit box that is formatted to validate a reference on a dialog sheet.

The RefEdit control on a UserForm does not automatically validate references. The Edit Box control in dialog sheets allows you to set up a reference validation. This control returns a message and forces you to enter a valid reference before it dismisses the dialog box. The following example uses the TypeName function to determine whether the data in the RefEdit control is a valid reference.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/

Creating a UserForm That Uses the TypeName Function to Validate a Reference

To use the example, follow these steps:

1. Start Microsoft Excel 98.

2. Press OPTION+F11 to start the Visual Basic Editor.

3. On the Insert menu, click UserForm.

4. Draw a RefEdit control on the UserForm. Draw a CommandButton on the

   UserForm.

   following:

      Private Sub CommandButton1_Click()

         Dim y as Range

         On Error Resume Next

         Set y = Range(Refedit1.value)

         If TypeName(y) <> "Range" Then
            MsgBox "That is Not a Valid Range"
            RefEdit1.SetFocus
         Else
            MsgBox "That is a Valid Range"
         End If

      End Sub

   UserForm.

   the CommandButton.

   The following message appears:

      This is Not a Valid Range

   CommandButton.

   The following message appears:

      This is a Valid Range

STATUS

This behavior is by design in Microsoft Excel 98 Macintosh Edition.

MORE INFORMATION

This issue occurs because focus remains on the edit box even when you select another object. When you select another object, the dialog box is placed in "semiselect" mode. When the dialog box is in this mode, a macro cannot run.

Example of the Behavior

To see an example of the symptom that is described in this article, follow these steps:

 1. Start Microsoft Excel 98 and create a new workbook.

 2. Press OPTION+F11 to start the Visual Basic Editor.

 3. On the Insert menu, click Module. Type the following in the module:

       Sub Test()
       Msgbox "Hello"
       End Sub

 4. On the File menu, click "Close and Return to Microsoft Excel."

 5. Hold down the CONTROL key and click one of the sheet tabs. On the
    shortcut menu that appears, click Insert.

 6. Click MS Excel 5.0 Dialog in the Insert dialog box, and click OK.

 7. Click OptionButton on the Forms toolbar, and then draw an OptionButton
    on the Dialog Box.

    NOTE: If the Forms toolbar is not visible, point to Toolbars on the
    View menu and click Forms.

 8. Click Edit Box on the Forms toolbar, and then draw an Edit Box on the
    Dialog Box.

 9. Hold down the CONTROL key and click the OptionButton. Click Assign
    Macro. In the Assign Macro dialog, select test in the Macro name list,
    and click OK.

    Control tab. Under Edit validation, select Reference.  Click OK.

When you click the OptionButton, the dialog box closes and the macro does not run.

REFERENCES

For more information about UserForms, from the Visual Basic Editor, click Contents And Index on the Help menu (or on the Balloon Help menu if you are using a version of the Macintosh operating system earlier than 8.0), click the Index button in Microsoft Excel Help, type the following text

   userform window

For more information about the TypeName function, click the Office Assistant, type "How do I use the TypeName function?" click Search, and then click to view "TypeName Function."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q179216
   TITLE     : OFF98: How to Use the Microsoft Office Installer Program

Keywords          : kbcode kbprg kbdta OffVBA 
Version           : MACINTOSH:98
Platform          : MACINTOSH
Issue type        : kbprb

Last Reviewed: May 18, 1999