Hide Your Input, and it’s Pretty Common

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.

 

Your download file is called Steele_AA200503.accdb

About Doug Steele

Doug Steele has worked with databases-both mainframe and PC-for many years with a major international oil company. In a previous life, he taught introductory computer programming at the University of Waterloo. Microsoft has recognized him as an Access MVP for his contributions to the user community over the years.

Doug is the author of Access Solutions with Arvin Meyer.

This entry was posted in VBA. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.