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", _

WindowMode:=acDialog

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

DoCmd.Close

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 = _

gclsInputBox.BackColor

.Caption = gclsInputBox.Title

With .lblPrompt

.Caption = gclsInputBox.Prompt

.FontItalic = gclsInputBox.PromptItalic

.FontName = gclsInputBox.PromptFontName

.FontSize = gclsInputBox.PromptSize

.FontUnderline = _

gclsInputBox.PromptUnderline

.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 = _

gclsInputBox.CancelCaption

End With

Me.txtValue.SetFocus

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:

Private Type CHOOSECOLORSTRUCTURE

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_PREVENTFULLOPEN = &H4

Private Const CC_SHOWHELP = &H8

Private Const CC_ENABLEHOOK = &H10

Private Const CC_ENABLETEMPLATE = &H20

Private Const CC_ENABLETEMPLATEHANDLE = &H40

Private Const CC_SOLIDCOLOR = &H80

Private Const CC_ANYCOLOR = &H100

Private Declare Function ChooseColor _

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

pChoosecolor As CHOOSECOLORSTRUCTURE _

) 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

Dim typCS As CHOOSECOLORSTRUCTURE

With typCS

.lStructSize = Len(typCS)

.hwnd = hWndAccessApp

.Flags = CC_ANYCOLOR Or CC_RGBINIT

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

End With

lngReturn = ChooseColor(typCS)

If lngReturn = 0 Then

ColorSelector = RGB(255, 255, 255)

Else

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.

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.

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.