Microsoft KB Archive/182870

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

Article ID: 182870

Article Last Modified on 6/17/2005

-

APPLIES TO


 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q182870



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



After you click one of these controls, the macro does not run and the dialog box closes. To see an example of this behavior, see the "More Information" section in this article.



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. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements.

Creating a UserForm That Uses the TypeName Function to Validate a Reference
To use the example, follow these steps:

 Start Microsoft Excel 98. Press OPTION+F11 to start the Visual Basic Editor. On the Insert menu, click UserForm.</li> Draw a RefEdit control on the UserForm. Draw a CommandButton on the UserForm.</li>  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 </li> Close the code window. Select the UserForm and press F5 to run the UserForm.</li> Type test in the RefEdit box. Click the CommandButton.

The following message appears:

This is Not a Valid Range

</li> Type $a$1 in the RefEdit box. Click the CommandButton.

The following message appears:

This is a Valid Range

</li></ol>

<div class="status_section">

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

<div class="moreinformation_section">

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:

<ol> Start Microsoft Excel 98 and create a new workbook.</li> Press OPTION+F11 to start the Visual Basic Editor.</li>  On the Insert menu, click Module. Type the following in the module: Sub Test Msgbox "Hello" End Sub </li> On the File menu, click "Close and Return to Microsoft Excel."</li> Hold down the CONTROL key and click one of the sheet tabs. On the shortcut menu that appears, click Insert.</li> Click MS Excel 5.0 Dialog in the Insert dialog box, and click OK.</li> 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.</li> Click Edit Box on the Forms toolbar, and then draw an Edit Box on the Dialog Box.</li> 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.</li> Select the Edit box. On the Format menu, click Control. Click the Control tab. Under Edit validation, select Reference. Click OK.</li> Click the Run Dialog button on the Forms toolbar.</li></ol>

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

<div class="references_section">