Hide Your Input, and it’s Pretty Common

<< Click to Display Table of Contents >>

Navigation:  VBA >

Hide Your Input, and it’s Pretty Common

200503_ds3 Doug Steele          

This month, Doug Steele looks at how you can enhance the built-in InputBox, as well as how to programmatically call a couple of the standard Windows dialogs (the Color and Font dialogs).

Is there any way that I can use the InputBox, but conceal what's being keyed in, like what happens when you're keying in a password?

Unfortunately, VBA's built-in InputBox doesn't provide much flexibility. You can change its title, its prompt, its default value, and where it appears on the screen–but that's about it. Even some of the flexibility that the InputBox says it supports, it doesn't actually support: While the Help file talks about the capability of providing arguments that specify a Help file and Help context for the InputBox, Access actually ignores those values, and no Help button appears on the InputBox. It's still possible to accomplish what you're trying to do, but you'll have to roll your own form.

Creating the form is pretty simple, so I won't help you with that. All that's needed is a simple form with two command buttons (OK and Cancel), a label, and a textbox (see Figure 1). Set the form's Format properties as shown in Figure 2.

Figure 1
Figure 2

When you open the form, you'll see that it looks pretty much the same as the built-in InputBox. Of course, you're not done. First of all, typing anything into the textbox isn't concealed, which is what you want, and there's no easy way to get the value that you type into the textbox.

Handling the first omission (what's typed in the textbox not being concealed) is pretty simple: Set the textbox control's InputMask property to "Password". Getting the value back from the form involves a little more work.

Now, I'm not going to say that my approach is the best, but the method that I use works successfully for me. For instance, I could simply create a form with preset properties, but I want to be a little more flexible: I'm going to make many of the properties on the InputBox replacement form be programmable. I find using a class module lets me control managing those properties a little better.

One warning: The following code doesn't show all of the properties of the form that can be changed. Check the accompanying database in the download to see more. Also, since Access didn't implement the Help button on the InputBox, I'm going to ignore it too, although you're welcome to add it yourself!

So what properties of the InputBox do I control? Obviously, my InputBox replacement needs to have at least the same capabilities as the built-in InputBox, so I have to be able to set the title, prompt, default value, and x and y positions for the form. Since the whole point of this InputBox replacement was to allow control over the InputMask (to allow you to hide what's being keyed there), I want to be able to set that property as well. I decided that I also wanted to control the font used for the prompt as well as the text being entered and, furthermore, I wanted to allow those formats to be set independently. I allowed for the form color to be changed programmatically, too. Finally, I provided the ability to change the button captions from "OK" and "Cancel" to any other text that you might want.

The basis for getting the value passed back from the InputBox replacement form to the calling function is to open the form in Dialog mode. Opening a form in Dialog mode means that no further processing will take place in the program until the form is closed. By having the form write to a public variable (or, in this case, set a property in the class), I can determine what was typed into the box.

The class I've created, cInputBox, exists simply to allow me to control all of the properties mentioned earlier. Any time the class is created, the class calls a subroutine named Reset to set a series of internal, module-level variables to the default values I want to use. (Hint: This means that if you don't like my defaults, you should change the code in the Reset routine.)

Public Sub Reset()

 mbooPromptFontItalic = False

 mbooPromptFontUnderLine = False

 mbooTextFontItalic = False

 mbooTextFontUnderLine = False

 mintPromptFontSize = 8

 mintPromptFontWeight = 400

 mintTextFontSize = 8

 mintTextFontWeight = 400

 mlngBackColor = -2147483633

 mlngPromptFontColor = 0

 mlngTextFontColor = 0

 mstrCancelCaption = "Cancel"

 mstrDefaultValue = vbNullString

 mstrInputMask = vbNullString

 mstrOKCaption = "OK"

 mstrPrompt = "What value?"

 mstrPromptFontName = "Arial"

 mstrTextFontName = "Arial"

 mstrTitle = "Doug Steele's InputBox Replacement"

 mlngXPos = -1

 mlngYPos = -1

End Sub

Almost all of the rest of the class module is code that retrieves or sets these variables through a set of property routines. When code reads the class's BackColor property, for instance, the BackColor's Get routine is called and the value of the mlngBackColor is returned to the calling code. When code sets the class's BackColor property, the BackColor's Set routine is called and the value of mlngBackColor is set to whatever value is used by the calling code (the value is passed in through the parameter that I called NewColor):

Public Property Get BackColor() As Long

 BackColor = mlngBackColor

End Property

Public Property Let BackColor(NewColor As Long)

 mlngBackColor = NewColor

End Property

When using the class, once I've set all of the properties the way I want them, I call the InputBox function of the class, which looks like this:

Public Function InputBox() As String

 DoCmd.OpenForm FormName:="frmInputBox", _


 InputBox = mstrResponse

End Function

As you can see, the InputBox routine opens the form in Dialog mode. Because I've opened in the form in Dialog mode, all processing stops on the OpenForm line until the user clicks on the form's OK or Cancel button.

The OK button of the frmInputBox form contains code to set mstrResponse equal to whatever was typed into the textbox:

Private Sub cmdOk_Click()

 gclsInputBox.Response = Me.txtValue


End Sub

The only other "interesting" code in frmInputBox is the code that reads the properties from the class and sets the form's properties accordingly:

Private Sub Form_Load()

 With Me

   .Detail.BackColor = _


   .Caption = gclsInputBox.Title

   With .lblPrompt

     .Caption = gclsInputBox.Prompt

     .FontItalic = gclsInputBox.PromptItalic

     .FontName = gclsInputBox.PromptFontName

     .FontSize = gclsInputBox.PromptSize

     .FontUnderline = _


     .FontWeight = gclsInputBox.PromptWeight

     .ForeColor = gclsInputBox.PromptColor

   End With

   .txtValue = gclsInputBox.DefaultValue

   With .txtValue

     .InputMask = gclsInputBox.InputMask

     .FontItalic = gclsInputBox.TextItalic

     .FontName = gclsInputBox.TextFontName

     .FontSize = gclsInputBox.TextSize

     .FontUnderline = gclsInputBox.TextUnderline

     .FontWeight = gclsInputBox.TextWeight

     .ForeColor = gclsInputBox.TextColor

   End With

   .cmdOk.Caption = gclsInputBox.OKCaption

   .cmdCancel.Caption = _


 End With


End Sub

So how do you use this code? Copy the form frmInputBox, the module mdlInputBox, and the class module cInputBox from the sample database into your own database. Where you want to invoke the InputBox replacement, don't use code like this:

Dim strResponse As String

 strResponse = InputBox("Type in a value:", _

   "Testing my input box")

Instead, use code like this:

Dim strResponse As String

 Set gclsInputBox = New cInputBox

 With gclsInputBox

   .Title = "Testing my input box"

   .Prompt = "Type in a value:"

   .InputMask = "Password"

 End With

 strResponse = gclsInputBox.InputBox

 Set gclsInputBox = Nothing

And, yes, I'll admit that this solution is a little messy because it has that global variable gclsInputBox that's defined in mdlInputBox. But it does work and not only meets your goal for a password-like input box, but also provides you with even more flexibility.

Your solution involves setting Color and Font properties. How can I know what values are valid?

The values Access uses for Colors and Font information are standard Windows values. However, there's a simpler way than having to type in new variable names or remember cryptic numbers: You can invoke the standard "picker" dialogs that are included in the comdlg32.dll that comes with Windows (shown in Figure 3 and Figure 4).

Figure 3
Figure 4

The Color Chooser is the simpler of the two to work with. First, you must define some constants and then declare the CHOOSECOLOR structure and the ChooseColor API:


 lStructSize As Long

 hwnd As Long

 hInstance As Long

 rgbResult As Long

 lpCustColors As String

 Flags As Long

 lCustData As Long

 lpfnHook As Long

 lpTemplateName As String

End Type

Private Const CC_RGBINIT = &H1

Private Const CC_FULLOPEN = &H2


Private Const CC_SHOWHELP = &H8

Private Const CC_ENABLEHOOK = &H10

Private Const CC_ENABLETEMPLATE = &H20


Private Const CC_SOLIDCOLOR = &H80

Private Const CC_ANYCOLOR = &H100

Private Declare Function ChooseColor _

 Lib "comdlg32.dll" Alias "ChooseColorA" ( _


) As Long

Once that's done, you have to do two things: Initialize the structure, and then call the API. Here's a typical example:

Public Function ColorSelector() As Long

Dim lngReturn As Long


 With typCS

   .lStructSize = Len(typCS)

   .hwnd = hWndAccessApp


   .lpCustColors = String$(16 * 4, 0)

 End With

 lngReturn = ChooseColor(typCS)

 If lngReturn = 0 Then

   ColorSelector = RGB(255, 255, 255)


   ColorSelector = typCS.rgbResult

 End If

End Function

I've set this function up to return the RGB value of the selected color. In the event of an error occurring, the function returns the RGB value for White (an arbitrary choice on my part).

The more curious among you may want to know what Flag values are possible. I've listed the values in Table 1.

Table 1. Valid values for Flag in the CHOOSECOLOR structure.




Causes the dialog box to display all available colors in the set of basic colors.


Enables the hook procedure specified in the lpfnHook member of this structure. This flag is used only to initialize the dialog box.


Indicates that the hInstance and lpTemplateName members specify a dialog box template to use in place of the default template. This flag is used only to initialize the dialog box.


Indicates that the hInstance member identifies a data block that contains a preloaded dialog box template. The system ignores the lpTemplateName member if this flag is specified. This flag is used only to initialize the dialog box.


Causes the dialog box to display the additional controls that allow the user to create custom colors. If this flag isn't set, the user must click the Define Custom Color button to display the custom color controls.


Disables the Define Custom Color button.


Causes the dialog box to use the color specified in the rgbResult member as the initial color selection.


Causes the dialog box to display the Help button. The hwndOwner member must specify the window to receive the HELPMSGSTRING registered messages that the dialog box sends when the user clicks the Help button.


Causes the dialog box to display only solid colors in the set of basic colors.

By the way, while we're on the topic of colors, you may not be aware that there are predefined values that can be specified so that the user's preferences are used. Instead of setting a property to some specific color, these values tell the property to pick up the value used in some Windows setting. I've listed those values in Table 2.

Table 2. System color values.






Scroll bar color



Desktop color



Color of the title bar for the active window



Color of the title bar for the inactive window



Menu background color



Window background color



Window frame color



Color of text on menus



Color of text in windows



Color of text in caption, size box, and scroll



Border color of active window



Border color of inactive window



Background color of multiple-document interface (MDI) application



Background color of items selected in a control



Text color of items selected in a control



Color of shading on the face of command buttons



Color of shading on the edge of command buttons



Grayed (disabled) text



Text color on push buttons



Text color of text in an inactive caption



Highlight color for 3-D display elements



Darkest shadow color for 3-D display elements



Second lightest 3-D color after vb3DHighlight



Color of text in ToolTips



Background color of ToolTips

As an example, if you want the background of your form to be whatever color the user has specified for the Windows background color, you can set the BackColor property to vbWindowBackground (or -2147483643), the value for screen element Window in Table 2.

You have some flexibility in how you use these constants. The Windows system color value refers only to the color of the screen element listed, not to the type of object it can be assigned to. For example, you could set the BackColor property for a textbox to the Windows system color for scroll bars, the desktop, or any other screen element. The key point about using these values is that these values stay constant, regardless of the user's color settings.

As I mentioned earlier, the ChooseFont API call isn't nearly as straightforward as ChooseColor, partly because the ChooseFont call doesn't just return a single value. I'm not going to reproduce the code necessary to use the ChooseFont call here; you can see it in mdlFontPicker in the download database. The function FontSelector expects a structure to be passed to it. If the structure is populated with values, those will be the values used to initialize the Choose Font dialog. After FontSelector finishes running, whatever values were selected in the dialog by the user are passed back through the same structure.


Your download file is called Steele_AA200503.accdb in the folder SA2005-03

This can be purchased with all the other downloads on this page