Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Access Answers: Hide Your Input, and it’s Pretty Common
Collapse the table of content
Expand the table of content

Access Answers: Hide Your Input, and it's Pretty Common

Office 2003
This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

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

Click the Download button for the code (SA05-03Steele.exe)

To find out more about Smart Access and Pinnacle Publishing, visit their Web site at http://www.pinpub.com/

Note: This is not a Microsoft Corporation Web site. Microsoft is not responsible for its content.

This article is reproduced from the March 2005 issue of Smart Access. Copyright 2005, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.

Show:
© 2015 Microsoft