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

• 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/overview/overview.asp

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

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.



5. Double-click the CommandButton to view the underlying code. Type the 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 




6. Close the code window. Select the UserForm and press F5 to run the UserForm.



7. Type test in the RefEdit box. Click the CommandButton.
   
   The following message appears:

   This is Not a Valid Range




8. Type $a$1 in the RefEdit box. Click the 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

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.



10. Select the Edit box. On the Format menu, click Control. Click the Control tab. Under Edit validation, select Reference. Click OK.



11. Click the Run Dialog button on the Forms toolbar.

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

Q179216 OFF98: How to Use the Microsoft Office Installer Program

Additional query words: XL98 dialogsheet dialogsheets

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