XL5: Creating a Masked Password Dialog Box in Visual Basic

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows NT, version 5.0

SUMMARY

In Microsoft Excel, you can create a custom dialog box to prompt a user for information using text boxes, buttons, or other dialog box controls. Normally, when you type text in an edit box, the text is displayed as you type. However, you can use a Visual Basic, Applications Edition procedure to create the effect of a hidden, or "masked," edit box. This can be useful for creating a password dialog box, where you do not want the text entered in an edit box to be "visible." The following information describe how to create this effect.

NOTE: This functionality is built into Microsoft Excel for Windows 95, version 7.0.

MORE INFORMATION

To "mask" an edit box, you can create two edit boxes; one that's visible and one that is hidden. While the user enters the password in the hidden edit box, a Visual Basic procedure enters "masking" characters in the visible edit box.

Visual Basic Code Example

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 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 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/default.asp

1. Create a dialog sheet with a dialog frame that has an OK button, a Cancel button, and two edit boxes. The edit boxes should be the same size.

2. Drag one of the edit boxes off of the dialog frame on the dialog sheet. Select this edit box. In the Name box, type "EditHidden" (without the quotation marks) and press ENTER.

3. Position the second edit box on the dialog frame where you want the password to be entered.

4. On the dialog frame, select the second edit box. In the Name box, type "EditShown" (without the quotation marks) and press ENTER.

5. From the Tools menu, choose Tab Order. From the Tab Order list, select EditHidden. Click the up arrow button until EditHidden is at the top of the Tab Order list. Choose OK.

6. From the Insert menu, choose Macro, and then choose Module, to insert a new module sheet. In the new module, enter the following:

         ' Dimension variable as public
         Public CancelFlag As Boolean
   

         Sub Main()
            ' Dimension variables.
            Dim password As String
   
            ' Set initial conditions.
            CancelFlag = False
            DialogSheets(1).EditBoxes("EditHidden").Text = ""
   
            ' Loop while password is incorrect and cancel button not pressed.
            While password <> "hello" And CancelFlag = False
   
               ' Set conditions for dialog box display.
               DialogSheets(1).EditBoxes("EditShown").Text = ""
               DialogSheets(1).EditBoxes("EditShown").Enabled = False
   
               ' Display the dialog box.
               DialogSheets(1).Show
   
               password = DialogSheets(1).EditBoxes("EditHidden").Text
   
            ' Continue loop until correct password is entered.
            Wend
   
            ' Check to see if the Cancel button is chosen.
            If CancelFlag = False Then
   
               ' If loop has ended and Cancel button has not been pressed,
               ' then password is correct.
               MsgBox "Correct password entered"
   
            Else
   
               MsgBox "Dialog was canceled."
   
            End If
         End Sub
   
         ' Macro assigned to EditHidden Edit box.
         Sub PasswordMask()
            ' Dimension variables.
            Dim MaskString As String, i As Integer
   
            MaskString = ""
   
            ' Match mask string length to length of text entered.
            For i = 1 To Len(DialogSheets(1).EditBoxes("EditHidden").Text)
               MaskString = MaskString & "*"
            Next i
   
            ' Enter mask string in EditShown Edit box.
            DialogSheets(1).EditBoxes("EditShown").Text = MaskString
   
         End Sub
   
         ' Macro assigned to the Cancel button.
         Sub Canceled()
   
            ' Cancel chosen, set CancelFlag value to True.
            CancelFlag = True
   
         End Sub
   
   

7. Select the dialog sheet tab to active the sheet that contains your dialog box. On the dialog sheet, select the "EditHidden" edit box, and choose Assign Macro from the Tools menu. From the Macro Name/Reference list, select PasswordMask and choose OK.

8. On the dialog box, select the Cancel button, and choose Assign Macro from the Tools menu. From the Macro Name/Reference list, select Canceled and choose OK.

9. From the Tools menu, choose Macro. From the Macro Name/Reference list, select Main and choose Run.