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

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.
Draw a RefEdit control on the UserForm. Draw a CommandButton on the UserForm.

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

Close the code window. Select the UserForm and press F5 to run the UserForm.
Type test in the RefEdit box. Click the CommandButton.

The following message appears:
This is Not a Valid Range
Type $a$1 in the RefEdit box. Click the CommandButton.

The following message appears:
This is a Valid Range

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:

Start Microsoft Excel 98 and create a new workbook.
Press OPTION+F11 to start the Visual Basic Editor.

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

       Sub Test()
       Msgbox "Hello"
       End Sub

On the File menu, click "Close and Return to Microsoft Excel."
Hold down the CONTROL key and click one of the sheet tabs. On the shortcut menu that appears, click Insert.
Click MS Excel 5.0 Dialog in the Insert dialog box, and click OK.
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.
Click Edit Box on the Forms toolbar, and then draw an Edit Box on the Dialog Box.
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.
Select the Edit box. On the Format menu, click Control. Click the Control tab. Under Edit validation, select Reference. Click OK.
Click the Run Dialog button on the Forms toolbar.

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

and then click Show Topics. Select the "UserForm Window" topic, and click Go To. If you are unable to find the information you need, ask the Office Assistant.

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:

179216 OFF98: How to Use the Microsoft Office Installer Program