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.
Constant | Description |
CC_ANYCOLOR | Causes the dialog box to display all available colors in the set of basic colors. |
CC_ENABLEHOOK | Enables the hook procedure specified in the lpfnHook member of this structure. This flag is used only to initialize the dialog box. |
CC_ENABLETEMPLATE | 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. |
CC_ENABLETEMPLATEHANDLE | 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. |
CC_FULLOPEN | 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. |
CC_PREVENTFULLOPEN | Disables the Define Custom Color button. |
CC_RGBINIT | Causes the dialog box to use the color specified in the rgbResult member as the initial color selection. |
CC_SHOWHELP | 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. |
CC_SOLIDCOLOR | 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.
IntrinsicConstant | Value | Description |
vbScrollBars | -2147483648 | Scroll bar color |
vbDesktop | -2147483647 | Desktop color |
vbActiveTitleBar | -2147483646 | Color of the title bar for the active window |
vbInactiveTitleBar | -2147483645 | Color of the title bar for the inactive window |
vbMenuBar | -2147483644 | Menu background color |
vbWindowBackground | -2147483643 | Window background color |
vbWindowFrame | -2147483642 | Window frame color |
vbMenuText | -2147483641 | Color of text on menus |
vbWindowText | -2147483640 | Color of text in windows |
vbTitleBarText | -2147483639 | Color of text in caption, size box, and scroll |
vbActiveBorder | -2147483638 | Border color of active window |
vbInactiveBorder | -2147483637 | Border color of inactive window |
vbApplicationWorkspace | -2147483636 | Background color of multiple-document interface (MDI) application |
vbHighlight | -2147483635 | Background color of items selected in a control |
vbHighlightText | -2147483634 | Text color of items selected in a control |
vbButtonFace | -2147483633 | Color of shading on the face of command buttons |
vbButtonShadow | -2147483632 | Color of shading on the edge of command buttons |
vbGrayText | -2147483631 | Grayed (disabled) text |
vbButtonText | -2147483630 | Text color on push buttons |
vbInactiveCaptionText | -2147483629 | Text color of text in an inactive caption |
vb3DHighlight | -2147483628 | Highlight color for 3-D display elements |
vb3DDKShadow | -2147483627 | Darkest shadow color for 3-D display elements |
vb3DLight | -2147483626 | Second lightest 3-D color after vb3DHighlight |
vbInfoText | -2147483625 | Color of text in ToolTips |
vbInfoBackground | -2147483624 | 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.