We have already learned how to create Menus and Toolbars through the following Articles:

 

Calendar and Toolbars

Custom Menus and Toolbars

Custom Menus and Toolbars-2

 

I think InputBox() function is one of the few things that we come across when start learning VBA and discard it later altogether when we see more exiting objects like MsgBox(). Probably its usage is not properly understood and it doesn’t deserve this neglect. The usage of Inputbox() function is very simple like MsgBox() but seldom used in programs to gather user responses. Instead, MsgBox() is the most preferred control, which has a variety of options available to configure it with different options of Command Buttons. But these options are like OK, Cancel, Retry, Yes, No etc. MsgBox cannot be programmed to make selections from user defined Options like the facility provides by InputBox().

 

We have learned how to use Office Assistant with preferred default Animation character instead of MsgBox. We have also seen how to collect user responses using Office Assistant with Check-Boxes or Option Balloon Menus in them. But, I admit that the VBA routines involved in these methods are complex and needs some time to understand the intricacies. But, once the program is in place the usage is very simple and can be inserted freely in your Code across projects, when placed in a library database and when linked to your other projects.

 

Coming back to the Inputbox() function; let us look into a simple example in a sub-routine. A Report Command Button on the Main Switchboard when clicked; a small menu is displayed asking the User whether she would like to get (1) Report Preview or (2) Print the Report directly to the Printer or (3) Exit, if there is a change of mind.

 

The simple usage of InputBox() function Syntax is given below:

 

X = InputBox("Message Text","Title",Default Value)

 

The Title and Default Values parameters are Optional. There are other optional values as well. You may check for them in the Microsoft Access Help Documents.

 

The Message Text is displayed on the body of the Form and the Title text appears on the Title Bar of the Control. We have used 1 as third Parameter (Default Value) and that value is inserted in the Text Box as shown in the following example image:

 

 

The Code that brings this Form up on a Command Button Click EventProcedure is given below.

 

Private Sub cmdRpt_Click()
Dim varResponse as Variant

varResponse = InputBox("(1) Print Preview (2) Print (3) Exit", "Select 1/2/3",1)

Select Case varResponse
    Case 1
        DoCmd.OpenReport "Orders", acViewPreview
    Case 2
        DoCmd.OpenReport "Orders", acViewNormal
    Case 3
        Exit Sub
End Select

End Sub

 

When the User makes a selection as per the Options provided, the selected value is tested and executes the actions programmed in the Select Case. . .End Select statements.

 

Note: The Inputbox() function brings up a Form with its Border Style Property Value set as Dialog so that the Minimize/Maximize & Restore Buttons removed from the Title Bar except the Window Close control. You are not allowed to click on any other Form or Objects in the Application window before you are done with the InputBox() Function, indicating that this Form’s Modal Property is set to Yes. We have familiarized the usage of Pop up and other Property settings of the Form in our earlier discussions on Form Properties in the following Articles:

 

Forms and Custom Properties

Synchronized Floating Pop-up Form

Positioning Pop-up Forms

Change Form Modes on User Profile

 

While using Inputbox() this way in programs we must take care of few things to avoid some undesirable side effects and pay attention to give the Menu a better look as well.

 

First, we will take the side effects part. We have provided Option 3 to close the Dialog Box normally, if the User has a change of mind after bringing up the Menu. But, she may ignore option 3 in the Menu and may use the Window Close control on the Title bar or click on the Cancel Command Button instead; that can invite trouble in the Code.

 

Since we have defined the Option Values in the Menu as Integer (1,2 and 3) naturally we will think about using an Integer Type Variable to record the response of the User. But, the User may click on the Cancel Command Button or Window Close Title Bar control, without selecting any of the options displayed, to close the Window. In that case the InputBox() Function will return an Empty String ("") as response. If we have defined an Integer Variable to accept the returned value, then this will end up with a Type Mismatch Error and the program will crash.

 

We can use a string variable to accept the Empty string result to avoid this problem and the Select Case. . . statement will work without modification even though we are testing for Integer values rather than in Case "1" style. A reasonable Variable Type selection to record the user response in these circumstances is a Variant Type that can accept any data type.

 

With a little trick we can make the program to ignore the Cancel Button and Window Close Title Bar control Clicks and force the user to make a selection from the Menu Options only, if she want to get out of the Menu normally.

 

We have inserted 1 as third Optional Parameter Value to take care of the OK Command Button Click by the User just in case she doesn’t type a selection value from the Menu Options and simply hits the OK Button. If she types one of the Option values in the Text Box and hits the Enter key, as we expect her to do, that will return the value selected into the varResponse Variable and the InputBox control will close normally.

 

We will modify the above Code:

 

1. to make it a better looking real Menu
2. to ignore the Cancel Command Button and Window Close Title Bar control clicks.

 

1. To prepare the Menu we will define a string Variable and write the following expression before inserting it into the InputBox() Function:

    

   Dim strMenu as String

   strMenu = "1. Report Preview" & vbCr & vbCr
   
   strMenu = strMenu & "2. Print Report" & vbCr & vbCr

   strMenu = strMenu & "3. Exit"

 

2. We will put the InputBox() Function within a Do While . . . Loop with a condition so that the Function will keep repeatedly executing if the user doesn't make a valid selection from the Menu and discourage her from Clicking Window Close or Cancel Buttons to get out of the Dialog Box.

 

varResponse = ""

Do While Len(varResponse) = 0 Or varResponse < 1 Or varResponse > 3

   varResponse = InputBox(strMenu, "Select 1/2/3",1)

Loop

 

After the above changes the Menu will look like the following image:

 

 

The modified Code is given below:

 

Private Sub cmdRpt_Click()
'------------------------------------------------------------
'Author : a.p.r. pillai
'Date : March-2009
'URL  : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------
Dim varResponse As Variant
Dim strMenu As String

strMenu = "1. Report Preview" & vbCr & vbCrstrMenu = strMenu & "2. Print Report" & vbCr & vbCrstrMenu = strMenu & "3. Exit"

varResponse = ""
Do While Len(varResponse) = 0 Or varResponse < 1 Or varResponse > 3
    varResponse = InputBox(strMenu, "Select 1/2/3", 1)
Loop

Select Case varResponse
    Case 1
        DoCmd.OpenReport "Orders", acViewPreview
    Case 2
        DoCmd.OpenReport "Orders", acViewNormal
    Case 3
        Exit Sub
End Select

End Sub

 

Probably the next question comes into one's mind is, do we have to use the Menu Options always in the form 1,2,3 etc., can we use alphabets instead, like

 

R. Report Preview

 

P. Print Report

 

E. Exit ?

 

 

Yes, we can with few changes in the Code as given below:

 

strMenu = "R. Report Preview" & vbCr & vbCr

strMenu = strMenu & "P. Print Report" & vbCr & vbCr

strMenu = strMenu & "E. Exit"

varResponse= ""

Do While instr(1, "RPE",varResponse)=0 or len(varResponse)=0

   varResponse = InputBox(strMenu, "Select R/P/E", "R")

Loop

 

The Select Case. . . statement must be changed to:

 

Select Case varResponse

Case "R"

   DoCmd.OpenReport "Orders", acViewPreview

Case "P"

   DoCmd.OpenReport "Orders", acViewNormal

Case "E"

   Exit Sub

End Select

 

The changed Code with alphabets as Options is given below:

 

Private Sub cmdRpt_Click()
'------------------------------------------------------------
'Author : a.p.r. pillai
'Date : March-2009
'URL  : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------
Dim varResponse As VariantDim strMenu As String

strMenu = "R. Report Preview" & vbCr & vbCr
strMenu = strMenu & "P. Print Report" & vbCr & vbCr
strMenu = strMenu & "E. Exit"

varResponse = ""

Do While InStr(1, "RPE", varResponse) = 0 Or Len(varResponse) = 0
    varResponse = InputBox(strMenu, "Select R/P/E", "R")
Loop

Select Case varResponse
    Case "R"
        DoCmd.OpenReport "Orders", acViewPreview
    Case "P"
        DoCmd.OpenReport "Orders", acViewNormal
    Case "E"
        Exit Sub
End Select

End Sub

 

 

---

Cardinal Text Format in Access

Custom Report Wizard

Custom Made Form Wizard

Rounding Function MRound() of Excel

Custom Calculator and Eval() Function