XL97: How to Temporarily Hide a UserForm

• Microsoft Excel 97 for Windows

SUMMARY

In earlier versions of Microsoft Excel, if you use the Hide method to hide a custom dialog box, Microsoft Excel does not hide the dialog box until the macro that contains the Hide method is finished running.

In Microsoft Excel 97, if you use the Hide method to hide a UserForm, the UserForm is hidden immediately. You can redisplayed the UserForm later by using the Show method in the macro (or in another macro). You can also use the Unload statement to hide a UserForm; however, any settings in the UserForm are lost. Note that you may want to use the Unload statement if you want to reset the UserForm.

This article explains how to create a UserForm and contains a sample Visual Basic for Applications macro that temporarily hides the UserForm (using the Hide method and the Unload statement).

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications procedures 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

Creating the UserForm

1. Save and close any open workbooks, and then create a new workbook.

2. Start the Visual Basic Editor (press ALT+F11).

3. On the Insert menu, click UserForm.

1. Add a CommandButton control to the UserForm.

2. Change the properties of the CommandButton to the following.

         Property   Value
         --------------------
         Caption    Hide Form
         Name       cmdHide
   
   

3. Add another CommandButton control to the UserForm.

4. Change the properties of the CommandButton to the following.

         Property   Value
         ----------------------
         Caption    Unload Form
         Name       cmdUnload
   
   

5. Add a TextBox control to the UserForm.

Sample Macro for Hiding the UserForm

1. Double-click the cmdHide CommandButton on UserForm1.

2. Type the following code for the cmdHide Click event:

   Private Sub cmdHide_Click()

              UserForm1.Hide
          End Sub
   
   

3. Type the following code for cmdUnload Click event:

   Private Sub cmdUnload_Click()

              Unload UserForm1
          End Sub
   
   

4. On the Insert menu, click Module.

5. Type the following code into this module:

          Sub Show_Form()
      
              UserForm1.Show   'Display the UserForm
      
              Do
                  response = MsgBox("Do you want to redisplay the form?", _
                      vbYesNo)
      
                  If response = vbYes Then
      
                      UserForm1.Show  'Redisplay the UserForm.
      
                  End If
      
              Loop Until response = vbNo  'Do not redisplay the UserForm.
      
          End Sub
   
   

6. Run the Show_Form macro.

7. Type text in the TextBox control.

8. Click Hide Form.

9. When you are prompted whether to redisplay the UserForm, click Yes.

1. Click Unload Form.

2. When you are prompted whether to redisplay the UserForm, click Yes.

1. Click Unload Form.

2. When you are prompted whether to redisplay the UserForm, click No.

For additional information about hiding dialog boxes in earlier versions of Microsoft Excel, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q141506
   TITLE     : XL: How to Temporarily Hide a Dialog Box

REFERENCES

For more information about Hiding UserForms, click the Office Assistant, type "hide", click Search, and then click to view "Hide Method".

For more information about Unloading UserForms, click the Office Assistant, type "unload", click Search, and then click to view "Unload Statement".

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

   ARTICLE-ID: Q120802
   TITLE     : Office: How to Add/Remove a Single Office
               Program or Component